How to convert 1k to 1000 and 1m to 1000000 in Excel

How to convert 1k to 1000 and 1m to 1000000 in Excel

I have text data containing numbers with abbreviations for thousands (k) and millions (m).  I want to convert these to numeric values. For example 1.2k to 1200 and 1.8m to 1800000. How can I do that?

Solutions/Answers:

Answer 1:

You can use search to return the relative position in a string of the suffix. Then raise ten to that number time 3.

=LEFT(A1,LEN(A1)-1)*10^(SEARCH(RIGHT(A1),"km")*3)

enter image description here

Answer 2:

You can use this Formula to convert abbreviated data into Numbers:

enter image description here

  • Formula in Cell C80 & fill it down.

    =IFNA(LEFT(A80,LEN(A80)-1)*CHOOSE(MATCH(RIGHT(A80,1), {"K","M","B"},0),1000,1000000,1000000000),A80)

N.B.

  • MATCH(RIGHT(A80,1),{"K","M","B"},0),
    returns 1 for K, 2 for M & 3 for B.
  • CHOOSE returns 1,000, 1,000,000 or 1,000,000,000 which is multiplied by LEFT(A80,LEN(A80)-1).
  • LEFT removes the K or M.

Adjust cell references in the Formula as needed.

Our Awesome Tools

References