Converting number to text

  • Thread starter Thread starter saman110 via OfficeKB.com
  • Start date Start date
S

saman110 via OfficeKB.com

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.
 
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)
 
Thank you so much. It works great.
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)
Click to expand...
 
Back
Top