convert numbers with K and M

[Guest]

I have a column of text that represent numbers which have been abbreviated
with K (for thousands) and M (for millions). (e.g. 123400 is displayed as
123.4K, 123456000 is displayed as 123.456M) How can I convert these to
numbers?

 

[Dave Peterson]

I would do a few Edit|Replaces.

Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 000
replace all

edit|Replace
what: M
with: 000000
replace all

Then format them the way I want.

 

[Guest]

hi,
the mid function might work.
for your first example....=MID(E8,1,5)*1000
for your second example....=MID(E9,1,7)*1000000
cells E8 and E9 in the formulas above would contain the values you wish to
convert.
read up on the mid function for more info.
yes i know. might be a little work but there isn't any consistency.

regards
FSt1

 

[Fred Smith]

Try the following:

=left(a1,len(a1)-1)*if(right(a1,1)="K",1000,if(right(a1,1)="M",1000000,1))

 

[Fred Smith]

This works fine if there are no decimals, but would change 123.4K to 1234000,
not 123400.

 

[Ron Rosenfeld]

Try the following:

=left(a1,len(a1)-1)*if(right(a1,1)="K",1000,if(right(a1,1)="M",1000000,1))

This formula assumes that every number has either a K or M suffix. If there
are values that might be entered with no suffix, then:

=IF(ISNUMBER(-A1),--A1,LEFT(A1,LEN(A1)-1)*
IF(RIGHT(A1,1)="K",1000,IF(RIGHT(A1,1)="M",1000000,1)))

--ron

 

[Dave Peterson]

You're right.

Sorry.

This works fine if there are no decimals, but would change 123.4K to 1234000,
not 123400.

 

[Dave Peterson]

Actually, if all the numbers included a decimal point and a tenths digit, then I
just screwed up my number of 0's.

123000 would have to be displayed as 123.0K for this to work:


Select the range to fix
edit|Replace
what: . (decimal point)
with: (leave blank)
replace all

edit|Replace
what: K
with: 00
replace all

edit|Replace
what: M
with: 00000
replace all