UtterAccess Discussion Forums > converting number to english text

Full Version: converting number to english text

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

ian_john_walker

Aug 8 2006, 05:07 AM

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

Aug 8 2006, 05:23 AM

Hi Ian,

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

Which hopfully should be large enough.

Stu.

ian_john_walker

Aug 8 2006, 05:32 AM

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

Aug 8 2006, 05:48 AM

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

Aug 8 2006, 06:00 AM

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

Aug 8 2006, 06:12 AM

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(6) = " Quadrillion "
    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

Aug 8 2006, 06:52 AM

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(6) = " Quadrillion "
    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

Aug 8 2006, 07:42 AM

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

Aug 8 2006, 07:57 AM

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.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.