Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 wrote in message ... 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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Thu, 23 Aug 2007 12:20:02 -0700, Knows nothing about formulas <Knows nothing
about wrote: 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",1000 000000},2,FALSE)) --ron |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 -- Gary''s Student - gsnu200739 "Knows nothing about formulas" wrote: 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. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Aug 23, 3:20 pm, Knows nothing about formulas <Knows nothing about
wrote: 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",LE FT(I14,3)*1000000000))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how do i turn all letters into capital letters? | Excel Discussion (Misc queries) | |||
turn the letters N and Y into numbers just for totals | Excel Worksheet Functions | |||
Turn numbers into letters | Excel Discussion (Misc queries) | |||
create self-generating numbers with letters and numbers | Excel Discussion (Misc queries) | |||
Why do all my cells automatically turn into uppercase letters? | Excel Discussion (Misc queries) |