13m and 40k From Text To Number

[Sabidesk]

I watched the amazing tips at this video posted at your youtube channel: [h=1]<yt-formatted-string>Excel Magic Trick 981: 123.45M From Text To Number, 123,450,000 From Number to 123.45M</yt-formatted-string>[/h]but what if I have my table like this:

4m
284.6k
1.4m
10m
7.7m
698k
154k
2.7m
20k

in case I have a huge table that I can't edit every number with a single step, then How to apply a formula that converts both k and m automatically to numbers in this order?

Thanks in advance

 

[steve the fish]

Hi. There may be a shorter formula but this does it based on the examples given:

=IF(ISNUMBER(SEARCH("m",A1)),SUBSTITUTE(A1,"m","")*1000000,IF(ISNUMBER(SEARCH("k",A1)),SUBSTITUTE(A1,"k","")*1000))

 

[Sabidesk]

Wow... it works Thank you Steve, from the deep of my heart. Thank you so much

 

[Rick Rothstein]

Wow... it works Thank you Steve, from the deep of my heart. Thank you so much

If all of your numbers have either a "k" or an "m" at the end, then this shorter formula should also work...

=LEFT(A1,LEN(A1)-1)*1000^(2-(RIGHT(A1)="k"))

 

[Sabidesk]

If all of your numbers have either a "k" or an "m" at the end, then this shorter formula should also work...

=LEFT(A1,LEN(A1)-1)*1000^(2-(RIGHT(A1)="k"))

Thank you so much Rick, Really Appreciated