Full Version: converting number to english text
ian_john_walker
guys,

any way to convert a very large number into text, tried using =spellnumber(cell) but can't seem to get it to work

Ian
StuKiel
Hi Ian,

=Text(cell,"0") will work to somewhere around 9E+253

Which hopfully should be large enough.

Stu.
ian_john_walker
Sorry Stu,

In a rush to post I maybe didn't explain it enough, I would like it to display words not just the value in text format

Apologies

Ian
StuKiel
Ah, ok.

Just had a look at SpellNumber. How big are you wanting to go, and what is the problem you are having with it?

Stu.
ian_john_walker
well number is....

39,346,408,075,296,500,000,000

I think it might be just my version/settings of excel, when i put the formula in it stays in lower case, I've selected all the Add-ins but no joy.

It could be that the number is just too big
StuKiel
Ok,

I have altered SpellNumber to:
CODE
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(33) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
Place(7) = " Quintillion "
Place(8) = " Sextillion "
Place(9) = " Septillion "
'...etc. up to...
Place(33) = " Duotrigintillion "
' String representation of amount.
MyNumber = Format(Trim(Str(MyNumber)), "0")
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = 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 Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
SpellNumber = Dollars & Cents
End Function

I tested it with your number and came up with:

Thirty Nine Sextillion Three Hundred Forty Six Quintillion Four Hundred Eight Quadrillion Seventy Five Trillion Two Hundred Ninety Six Billion Five Hundred Million

I took the American (I know I should have used the English, but who else does?) from here .

You will need to add the rest of the names, but should get you started.

Stu.
StuKiel
Hi Ian,

Have just read your post again. You will need to put all of this code in a new module, creating a user defined function (UDF) to get this to work. As above you will need to populate Place(10) to Place(32) with the number names, see my post above for the source I used.

CODE
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Intgr, Temp
Dim DecimalPlace, Count
ReDim Place(33) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
Place(7) = " Quintillion "
Place(8) = " Sextillion "
Place(9) = " Septillion "
'...etc. up to...
Place(33) = " Duotrigintillion "
' String representation of amount.
MyNumber = Format(Trim(Str(MyNumber)), "0")
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Intgr = Temp & Place(Count) & Intgr
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
SpellNumber = Intgr
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

Note this only works for whole nunbers, decimals are trimmed away.

Stu.
ian_john_walker
blimey, why is there never an easy answer!

the above looks stunning but I don't have a clue what a module/UDF even is!!! curse my feeble brain!

Thanks for your help though, ultimately I got what I needed!

thanks

Ian
StuKiel
OK, glad I was some help.

Here is an expanation (I apologise for any technical errors or omissions).

A module is where Code is held. This can be written by a user or by recording a macro. A UDF is a User Defined Function, basically, some code which returns a result. Typing =Sum(cell_Range) will return the sum of all values in the cells cell_range, a UDF will perform other calculations on the data given to it, and then return a result.

To get to the code window press ALT+F11.

You can then Insert -> Module.

Copy the code above, into the code window (You may have to paste in to Word first, then copy and paste from there - This will ensure that carriage returns are in the right palce). Once you have your module with the UDF close the code window.

If you then type 39,346,408,075,296,500,000,000 into cell A1, and =SpellNumber(A1) into cell A2, viola, cell A2 should return the text.

Hope that is some help.

Stu.