Converting Numbers to Words Part IV

See Converting Numbers to Words Part III

On to the thousands:

Sub TEST_Thousands()
   
    Debug.Assert NumbersToWords(1000) = "one thousand"
    Debug.Assert NumbersToWords(1001) = "one thousand one"
    Debug.Assert NumbersToWords(1099) = "one thousand ninety-nine"
    Debug.Assert NumbersToWords(1200) = "one thousand two hundred"
    Debug.Assert NumbersToWords(1310) = "one thousand three hundred ten"
    Debug.Assert NumbersToWords(1999) = "one thousand nine hundred ninety-nine"
   
End Sub

Every triplet of numbers follow the same rules, and I’ve already tested 0-999. I need to take all that relevant code and put it in a callable function.

Function ProcessTriplet(ByVal dNumber As Double, Optional ByVal sSuffix As String) As String
       
    Dim sReturn As String
    Dim vaSingles As Variant, vaTens As Variant
   
    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 dNumber >= 100 Then
        sReturn = sReturn & vaSingles(dNumber \ 100) & " hundred "
        dNumber = dNumber - (dNumber \ 100) * 100
    End If
   
    If dNumber > 19 Then
        sReturn = sReturn & vaTens(dNumber \ 10)
        dNumber = dNumber - (dNumber \ 10) * 10
    End If
   
    If dNumber > 0 Then
        If Right(sReturn, 1) = "y" Then
            sReturn = sReturn & "-"
        End If
   
        sReturn = sReturn & vaSingles(dNumber)
    End If
   
    sReturn = sReturn & Space(1) & sSuffix
   
    ProcessTriplet = Trim(sReturn)
   
End Function

That’s the same old code, just put into a function. The triplet of numbers is passed in and the suffix (currently only “thousand”) is passed in. Now I just need to divide my number into triplets, process them, and concatenate the answers.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim sReturn As String
    Dim dRemainder As Double
   
    If dNumbers = 0 Then
        sReturn = "zero"
    Else
       
        dRemainder = dNumbers
       
        If dRemainder >= 1000 Then
            sReturn = ProcessTriplet(dRemainder \ 1000, "thousand")
            dRemainder = dRemainder - ((dRemainder \ 1000) * 1000)
        End If
       
        If dRemainder > 0 Then
            sReturn = sReturn & Space(1) & ProcessTriplet(dRemainder)
        End If
       
    End If
   
    NumbersToWords = Trim$(sReturn)
   
End Function

If my numbers is 1,000 or more, I process the thousands triplet and figure the remainder. Then I process the remainder as its own triplet. I guess processing triplets in this way makes my next set of tests already pass.

Sub TEST_TenThousands()

    Debug.Assert NumbersToWords(10000) = "ten thousand"
    Debug.Assert NumbersToWords(10001) = "ten thousand one"
    Debug.Assert NumbersToWords(20099) = "twenty thousand ninety-nine"
    Debug.Assert NumbersToWords(30200) = "thirty thousand two hundred"
    Debug.Assert NumbersToWords(42310) = "forty-two thousand three hundred ten"
    Debug.Assert NumbersToWords(99999) = "ninety-nine thousand nine hundred ninety-nine"

End Sub

Yep, already passing. On deck, the millions.

One Comment

  1. It’s a pity you can’t use .Net. There’s a very nice library called Humanizer that does this, as well as many other capabilities: http://humanizr.net/#number-to-words

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

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