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?

Solution 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),"kmbt")*3)

To handle ones with out the suffix:

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

Solution 2:

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

• 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.

Solution 3:

If the suffixes are real SI prefixes then you can use Excel's CONVERT() function to get the numeric values

=CONVERT(VALUE(LEFT(A2, LEN(A2) - 1)), RIGHT(A2, 1) & "m", "m")
         ▲                          ▲  ▲          ▲
         └─────────────┬────────────┘  └─────┬────┘
                     value         value suffix/SI prefix

The trick here is to convert km, Mm... to m, but of course instead of meter you can use any SI basic units. The thousand suffix must be k in lowercase for kilo, and the million suffix must be uppercase M for Mega