# SpellNumber Function In Excel



## Madilla (27 Nov 2007)

I am trying to convert numbers into text in excel. From a web search I see I should be able to use the SpellNumber function but this not seem to work for me. Anybody any ides how I could resolve this.


----------



## efm (27 Nov 2007)

I don't think that SpellNumber is a standard function in Excel - I think it's a VBA add-on.

Code for add-on [broken link removed]


----------



## Madilla (27 Nov 2007)

Thks I tried this and it works. The only problem I have now is that the conversion is to dollars and cents. I need to convert numbers to a just plain whole numbers but as I know nothing about VBA I have no idea how to amend the program.
Thks


----------



## ClubMan (27 Nov 2007)

Change all references to "dollars" to "euros"?


----------



## Madilla (27 Nov 2007)

Problem is I don't want euros I just want a whole number. Thks


----------



## ClubMan (27 Nov 2007)

Oh - sorry.


----------



## Merrion (27 Nov 2007)

So just have something like this:

 Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim PreDecimal, PostDecimal, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert PostDecimal and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
PostDecimal = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then PreDecimal = Temp & Place(Count) & PreDecimal
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

SpellNumber = PreDecimal & " " & PostDecimal
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
Sub Test()
End Sub 

Will you be using decimal places?


----------



## z103 (27 Nov 2007)

> Problem is I don't want euros I just want a whole number. Thks


Change all references to "dollars" to ""?


or

Public Function NumberToText(lNumber as long) as string
    Select Case lNumber
         Case 1: NumberToText = "One": Exit Function
         Case 2: NumberToText = "Two": Exit Function    
         Case 3: NumberToText = "Three": Exit Function

...etc....

         Case 22: NumberToText = "Twenty Two": Exit Function


...etc... 

    End Select

End Function

(That's a joke BTW )


----------



## efm (27 Nov 2007)

leghorn said:


> (That's a joke BTW )


 
Ahh.....the old one's are the best - you can't beat a good VB joke


----------



## ClubMan (27 Nov 2007)

efm said:


> Ahh.....the old one's are the best - you can't beat a good VB joke


I find _C#/.NET _ones funnier.


----------



## DrMoriarty (27 Nov 2007)

You _guys_...  

Madilla, I usually just select all the affected cells and from the Format/Cells options I choose the 'text' category. This means that e.g. numbers beginning or ending with a zero are displayed exactly as entered. Is that what you're after?


----------



## Satanta (27 Nov 2007)

DrMoriarty said:


> Is that what you're after?


Not really.

She wants a tool, similiar to the SpellNumber tool linked above, that will convert numbers (3, 5, etc.) to text (three, five, etc.) but without the "dollar" or "euro" formatting (just plain whole numbers) of the tool linked above.

On first glancing through the thread I assumed such a tool would easily be found through google, given a few of the manual solutions provided above I'm guessing it might not be so readily available.


----------



## ClubMan (27 Nov 2007)

certainly seems to throw up plenty of potential solutions...


----------

