Converting Numbers to Words Part III

See Converting Numbers to Words Part II

My tests work from 0-99. The next test will test numbers between 100-199.

Sub TEST_OneHundred()

    Debug.Assert NumbersToWords(100) = "one hundred"
    Debug.Assert NumbersToWords(110) = "one hundred ten"
    Debug.Assert NumbersToWords(119) = "one hundred nineteen"
    Debug.Assert NumbersToWords(120) = "one hundred twenty"
    Debug.Assert NumbersToWords(121) = "one hundred twenty-one"
    Debug.Assert NumbersToWords(150) = "one hundred fifty"
    Debug.Assert NumbersToWords(188) = "one hundred eighty-eight"
    Debug.Assert NumbersToWords(199) = "one hundred ninety-nine"

End Sub

A haphazard selection of numbers including the edge cases.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
   
    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("NA,NA,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
       
    If dNumbers >= 100 Then
        sReturn = "one hundred"
        If dNumbers Mod 100 <> 0 Then
            If dNumbers - 100 > 19 Then
                sReturn = sReturn & Space(1) & vaTens((dNumbers - 100) \ 10)
                If (dNumbers - 100) Mod 10 <> 0 Then
                    sReturn = sReturn & "-" & vaSingles((dNumbers - 100) - (((dNumbers - 100) \ 10) * 10))
                End If
            Else
                sReturn = sReturn & Space(1) & vaSingles(dNumbers - 100)
            End If
        End If
       
    ElseIf dNumbers > 19 Then
        sReturn = vaTens(dNumbers \ 10)
        If dNumbers Mod 10 <> 0 Then
            sReturn = sReturn & "-" & vaSingles(dNumbers - ((dNumbers \ 10) * 10))
        End If
    Else
        sReturn = vaSingles(dNumbers)
    End If
   
   
    NumbersToWords = Trim$(sReturn)
   
End Function

And all tests pass. Back in the first post of this series I said that I hoped it would be obvious when I need to refactor. Well if this isn’t a frying pan to the face, I don’t know what is. Way too much repetition, for one. I need to introduce a “remainder” variable, so that once I process the hundred part, I can send the remainder to process the tens, and the remainder from that to the less than 19 part.

Function NumbersToWords(ByVal dNumbers As Double) As String

    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
    Dim dRemainder As Double

    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")

    dRemainder = dNumbers

    If dRemainder >= 100 Then
        sReturn = "one hundred" & Space(1)
        dRemainder = dRemainder - (dRemainder \ 100) * 100
    End If

    If dRemainder > 19 Then
        sReturn = sReturn & vaTens(dRemainder \ 10)
        dRemainder = dRemainder - (dRemainder \ 10) * 10
    End If

    If dRemainder > 0 Then
        If Right(sReturn, 1) = "y" Then
            sReturn = sReturn & "-"
        End If

        sReturn = sReturn & vaSingles(dRemainder)
    End If

    NumbersToWords = Trim$(sReturn)

End Function

That looks much better, but it doesn’t pass the zero test. I don’t like special cases, but zero might just be one, so I’m going to force it. My conditional on whether to include a hyphen checks to see if the answer so far ends in “y”. That seems a little hokey, but it works. I could test for mod10 and set a Boolean variable in the If block above, but I’m not sure what I gain, so there it stays.

Refactoring in this way also makes the next bit of testing code painfully obvious. I’m hardcoding “one hundred”, but with vaSingles sitting right there, I don’t know why I can’t go above 199 pretty easily. So I’ll write that next test.

Sub TEST_Hundreds()

    Debug.Assert NumbersToWords(200) = "two hundred"
    Debug.Assert NumbersToWords(310) = "three hundred ten"
    Debug.Assert NumbersToWords(419) = "four hundred nineteen"
    Debug.Assert NumbersToWords(520) = "five hundred twenty"
    Debug.Assert NumbersToWords(621) = "six hundred twenty-one"
    Debug.Assert NumbersToWords(750) = "seven hundred fifty"
    Debug.Assert NumbersToWords(888) = "eight hundred eighty-eight"
    Debug.Assert NumbersToWords(999) = "nine hundred ninety-nine"

End Sub

Instead of hardcoding “one hundred”, I’ll pull the property number from vaSingles. This also shows my brute force zero fix.

Function NumbersToWords(ByVal dNumbers As Double) As String

    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
    Dim dRemainder As Double

    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")

    If dNumbers = 0 Then
        sReturn = "zero"
    Else

        dRemainder = dNumbers
   
        If dRemainder >= 100 Then
            sReturn = sReturn & vaSingles(dRemainder \ 100) & " hundred "
            dRemainder = dRemainder - (dRemainder \ 100) * 100
        End If
   
        If dRemainder > 19 Then
            sReturn = sReturn & vaTens(dRemainder \ 10)
            dRemainder = dRemainder - (dRemainder \ 10) * 10
        End If
   
        If dRemainder > 0 Then
            If Right(sReturn, 1) = "y" Then
                sReturn = sReturn & "-"
            End If
   
            sReturn = sReturn & vaSingles(dRemainder)
        End If
    End If

    NumbersToWords = Trim$(sReturn)

End Function

All tests pass. And the code doesn’t look too bad. Only infinity numbers left to test. Here’s what my main testing procedure looks like now, as if you couldn’t guess.

Sub TEST_All()

    TEST_Singles
    TEST_Tens
    TEST_OneHundred
    TEST_Hundreds

    Debug.Print "tests passed"

End Sub

2 Comments

  1. snb says:

    or ?

    vaTens = Split("  twenty thirty forty fifty sixty seventy eighty ninety")
  2. I remember posting a recursive function for this to StackOverflow a while ago:

    http://stackoverflow.com/a/5232125

    I think your code is more elegant, but you’ll run into issues with tens of thousands, and the hierarchical conventions for millions and billions. For example, you can express 2^50 as:

    “Eleven Hundred and Twenty-Five Trillion, Eight Hundred and Ninety-Nine Billion, Nine Hundred and Six Million, Eight Hundred and Forty-Two Thousand, Six Hundred and Twenty One Trillion, Ninety-Nine Billion, Five Hundred and Eleven Million, Six Hundred and Twenty-Seven Thousand, Seven Hundred and Seventy-Six”

    I do not anticipate that you will need to express this kind of number in text on a regular basis. However, there is some instructional value in demonstrating an application of recursion to beginners in programming.

    Public Function SayNumber(ByVal InputNumber As Double, _
                           Optional DecimalPlaces As Integer = 0 _
                              ) As String

    ' Return the integer portion of the number in formatted English words

    ' Return the fractional part of the number as 'point' and a series of
    ' single-numeral words, up to the precision specified by 'DecimalPlaces'

    '   SayNumber(17241021505)
    '   "Seventeen Billion, Two Hundred and Forty-One Million,
    '        Twenty-One Thousand, Five Hundred and Five"

    '   SayNumber(Sqr(2), 6)
    '   "One point Four One Four Two One Four"

    ' Note that nothing after the decimal point will be returned if InputNumber is an integer

    ' Nigel Heffernan, December 2008

    Application.Volatile False
    On Error Resume Next

    Dim arrDigits(0 To 9)   As String
    Dim arrTeens(10 To 19)  As String
    Dim arrTens(2 To 9)     As String

    Dim i            As Integer
    Dim i10          As Integer
    Dim i20          As Integer
    Dim dblRemainder As Double
    Dim dblMain      As Double
    Dim iRemainder   As Long
    Dim iMain        As Long
    Dim dblFraction  As Double
    Dim strFraction  As String

    Dim strMinus     As String
    Dim str1         As String
    Dim str2         As String
    Dim str3         As String

    If Application.EnableEvents = False Then
        Exit Function
    End If

    arrDigits(0) = "Zero"
    arrDigits(1) = "One"
    arrDigits(2) = "Two"
    arrDigits(3) = "Three"
    arrDigits(4) = "Four"
    arrDigits(5) = "Five"
    arrDigits(6) = "Six"
    arrDigits(7) = "Seven"
    arrDigits(8) = "Eight"
    arrDigits(9) = "Nine"
               
    arrTeens(10) = "Ten"
    arrTeens(11) = "Eleven"
    arrTeens(12) = "Twelve"
    arrTeens(13) = "Thirteen"
    arrTeens(14) = "Fourteen"
    arrTeens(15) = "Fifteen"
    arrTeens(16) = "Sixteen"
    arrTeens(17) = "Seventeen"
    arrTeens(18) = "Eighteen"
    arrTeens(19) = "Nineteen"

    arrTens(2) = "Twenty"
    arrTens(3) = "Thirty"
    arrTens(4) = "Forty"
    arrTens(5) = "Fifty"
    arrTens(6) = "Sixty"
    arrTens(7) = "Seventy"
    arrTens(8) = "Eighty"
    arrTens(9) = "Ninety"

    If InputNumber < 0 Then
        strMinus = "Minus "
        InputNumber = Abs(InputNumber)
    End If

    If DecimalPlaces < 1 Then
        strFraction = ""
    Else

        dblFraction = InputNumber - Fix(InputNumber)
        If dblFraction = 0 Then
            strFraction = ""
        Else
            strFraction = " point"
            str1 = Format(dblFraction, "0." & String(DecimalPlaces, "0"))
            For i = 1 To DecimalPlaces
                str2 = MID(str1, i + 2, 1)
                strFraction = strFraction & " " & arrDigits(CInt(str2))
                str2 = ""
            Next i
            str1 = ""
        End If
       
    End If

    If InputNumber < 10 Then

        str1 = arrDigits(InputNumber)
       
    ElseIf InputNumber < 19 Then

        str1 = arrTeens(InputNumber)
       
    ElseIf InputNumber < 100 Then
       
        iMain = InputNumber \ 10
        str1 = arrTens(iMain)
       
        iRemainder = InputNumber Mod 10
        If iRemainder > 0 Then
            str2 = "-" & arrDigits(iRemainder)
        End If

    ElseIf InputNumber < 1000 Then

        iMain = InputNumber \ 100
        str1 = arrDigits(iMain) & " Hundred"
       
        iRemainder = InputNumber Mod 100
        If iRemainder > 0 Then
            str2 = " and " & SayNumber(iRemainder)
        End If
       
    ElseIf InputNumber < 2000 Then

        iMain = InputNumber \ 100
        str1 = arrTeens(iMain) & " Hundred"
       
        iRemainder = InputNumber Mod 100
        If iRemainder > 0 Then
            str2 = " and " & SayNumber(iRemainder)
        End If
       
    ElseIf InputNumber < 1000000 Then

        iMain = InputNumber \ 1000
        str1 = SayNumber(iMain) & " Thousand"

        iRemainder = InputNumber Mod 1000
        If iRemainder > 0 Then
            str2 = ", " & SayNumber(iRemainder)
        End If
       
    ElseIf InputNumber < (10 ^ 9) Then

        iMain = InputNumber \ (10 ^ 6)
        str1 = SayNumber(iMain) & " Million"

        iRemainder = InputNumber Mod (10 ^ 6)
        If iRemainder > 0 Then
            str2 = ", " & SayNumber(iRemainder)
        End If
       
    ElseIf InputNumber < (10 ^ 12) Then  
    ' we'll hit the LongInt arithmetic operation limit at ~2.14 Billion

        str3 = Format(InputNumber, "0")
        dblMain = CDbl(Left(str3, Len(str3) - 9))
        str1 = SayNumber(dblMain) & " Billion"
       
        dblRemainder = CDbl(Right(str3, 9))
        If dblRemainder > 0 Then
            str2 = ", " & SayNumber(dblRemainder)
        End If
           
    ElseIf InputNumber < 1.79769313486231E+308 Then      
    ' This will generate a recursive string of 'Trillions'

        str3 = Format(InputNumber, "0")
        dblMain = CDbl(Left(str3, Len(str3) - 12))
        str1 = SayNumber(dblMain) & " Trillion"
       
        dblRemainder = CDbl(Right(str3, 12))
        If dblRemainder > 0 Then
            str2 = ", " & SayNumber(dblRemainder)
        End If
           
    Else ' exceeds the specification for double-precision floating-point variables

        str1 = "#Overflow."
           
    End If

    SayNumber = strMinus & str1 & str2 & strFraction

    End Function

    I will leave the corresponding code in French as an exercise for the reader.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

You must be logged in to post a comment.

Here's how to update your reports of company and nearly any web data: