Converting number to text

[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.

 

[squenson]

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)

 

[saman110 via OfficeKB.com]

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)