Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default turn letters into numbers

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default turn letters into numbers

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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default turn letters into numbers

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default turn letters into numbers

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i turn all letters into capital letters? KeithT Excel Discussion (Misc queries) 3 May 11th 07 03:13 PM
turn the letters N and Y into numbers just for totals Psycho-J Excel Worksheet Functions 4 July 21st 06 08:30 PM
Turn numbers into letters Albert Excel Discussion (Misc queries) 4 March 29th 06 03:05 AM
create self-generating numbers with letters and numbers cxlough41 Excel Discussion (Misc queries) 11 January 4th 06 02:16 AM
Why do all my cells automatically turn into uppercase letters? Tomcat Excel Discussion (Misc queries) 2 October 3rd 05 01:09 AM


All times are GMT +1. The time now is 02:12 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"