turn letters into numbers

[Guest]

Set up a table with numbers reading like this:
1.2K 1.5M 1.8B

How can I get Excel to replace the K (or M or B) with K equaling thousand (M
equaling million and B equaling billion) to times the number it is using.

in other words, trying to convert 1.2K in the cell say 1,200. Is this
possible? And do I make sense? Thanks for your help ahead of time.

 

[Bernard Liengme]

With the 1.2K (etc) in A1, use
=LEFT(A1,LEN(A1)-1)*IF(RIGHT(A1)="K",1000,IF(RIGHT(A1)="M",1000000,IF(RIGHT(A1)="B",1000000000,0)))
I am assuming you are in US and B=1E9 not 1E12
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Knows nothing about formulas" <Knows nothing about
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

Set up a table with numbers reading like this:
1.2K 1.5M 1.8B

How can I get Excel to replace the K (or M or B) with K equaling thousand (M
equaling million and B equaling billion) to times the number it is using.

in other words, trying to convert 1.2K in the cell say 1,200. Is this
possible? And do I make sense? Thanks for your help ahead of time.

With the value in A3:

=IF(ISNUMBER(A3),A3,LEFT(A3,LEN(A3)-1)*
VLOOKUP(RIGHT(A3,1),{"K",1000;"M",1000000;"B",1000000000},2,FALSE))


--ron

 

[Guest]

Install this macro, select some cells and try it out:

Sub fixum()
' gsnuxx
For Each r In Selection
v = r.Value
w = Right(v, 1)

Select Case w
Case "K"
v = Left(v, Len(v) - 1) * 1000
Case "M"
v = Left(v, Len(v) - 1) * 1000000
Case "B"
v = Left(v, Len(v) - 1) * 1000000000
Case Else
End Select

r.Value = v
Next
End Sub

 

[oatmeal]

Set up a table with numbers reading like this:
1.2K 1.5M 1.8B

How can I getExcelto replace the K (or M or B) with K equaling thousand (M
equaling million and B equaling billion) to times the number it is using.

in other words, trying to convert 1.2K in the cell say 1,200. Is this
possible? And do I make sense? Thanks for your help ahead of time.

=IF(RIGHT(I14,1)="K",LEFT(I14,3)*1000,IF(RIGHT(I14,1)="M",LEFT(I14,3)*1000000,IF(RIGHT(I14,1)="B",LEFT(I14,3)*1000000000)))