Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting number to text
Hello all
I have combination of numbers and text in several cells for ex. "325 Bob". how can I convert the numbers to text. Output ex. "three two five Bob" in the cell. thx. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200707/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting number to text
This user-defined function should do the trick. Press F11 to open VB, then
click on the menu Insert Module, then copy the following code to the right pane. Function SpellNumbers(s) Dim i As Long Dim sOutput As String sOutput = "" For i = 1 To Len(s) Select Case Mid(s, i, 1) Case 0 sOutput = sOutput & "zero " Case 1 sOutput = sOutput & "one " Case 2 sOutput = sOutput & "two " Case 3 sOutput = sOutput & "three " Case 4 sOutput = sOutput & "four " Case 5 sOutput = sOutput & "five " Case 6 sOutput = sOutput & "six " Case 7 sOutput = sOutput & "seven " Case 8 sOutput = sOutput & "eight " Case 9 sOutput = sOutput & "nine " Case Else sOutput = sOutput & Mid(s, i, 1) End Select Next i SpellNumbers = sOutput End Function In your spreadsheet, you can use =SpellNumbers("325 Bob") or =SpellNumbers(B3) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting number to text
Thank you so much. It works great.
squenson wrote: This user-defined function should do the trick. Press F11 to open VB, then click on the menu Insert Module, then copy the following code to the right pane. Function SpellNumbers(s) Dim i As Long Dim sOutput As String sOutput = "" For i = 1 To Len(s) Select Case Mid(s, i, 1) Case 0 sOutput = sOutput & "zero " Case 1 sOutput = sOutput & "one " Case 2 sOutput = sOutput & "two " Case 3 sOutput = sOutput & "three " Case 4 sOutput = sOutput & "four " Case 5 sOutput = sOutput & "five " Case 6 sOutput = sOutput & "six " Case 7 sOutput = sOutput & "seven " Case 8 sOutput = sOutput & "eight " Case 9 sOutput = sOutput & "nine " Case Else sOutput = sOutput & Mid(s, i, 1) End Select Next i SpellNumbers = sOutput End Function In your spreadsheet, you can use =SpellNumbers("325 Bob") or =SpellNumbers(B3) -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting text to number | Excel Worksheet Functions | |||
Converting number to text | Excel Discussion (Misc queries) | |||
converting text to number | Excel Discussion (Misc queries) | |||
CONVERTING NUMBER TO TEXT | Excel Worksheet Functions | |||
Converting text to a number | Excel Worksheet Functions |