Excel VBA Function - Convert Number to Text

Converting Number to Text

I had run across a question in a user group not too long ago, asking about how to change an arbitrary number to text (such as, "101" to "one hundred one"). Up for the challenge, here below is a short function for Excel VBA that will do just this.

If you are interested in this post strictly for the code, you are welcome to skip to the code. If you are using this code, please include the credit lines.

Figure 1

In dissecting the problem, we can draw from our own experiences on how to handle things. Beginning from zero, we know each single digit (0,1,2,3,4,5,6,7,8,9) has a name, as follows (see Figure 1).

The programmer in us might suggest this is all we need per digit, and that may be true, if we were working in something other than our common decimal system. If we were working in binary, for example, we would suggest the binary number, "10" would be called, "one zero", not what we would say in common English with regard to decimal numbers, "ten". This said, we'll need to continue on with the matrix for our "teen" numbers, 10 through 19 (Figure 2):

Figure 2

 With this tackled, we need to consider the remaining 'tens' from 20-90. The ones place, we have handled in Figure 1, so we are only concerned with how we name our 'tens' (Figure 3):

So far so good. Using what we've just considered, we can write all numbers from 0 through 99.

Figure 3

Moving to the 'hundreds' place, we realize that this digit is simply what we would call the first digit, with 'hundred' after. i.e. "100" would be written as "one hundred"; "900" would be "nine hundred". "876" would be "eight hundred seventy six". With this knowledge, we know we can leverage what we've already considered for digits 1 through 9.

We can now write all numbers from 0 (zero) through 999 (nine hundred ninety nine).

Moving now into the thousands place, we have a different pattern to consider. It is very common to separate 'large' numbers into three digit groups separated by commas (in English). These sets of three numbers are called "periods". I looked it up.

A number such as "123" only has one 'period' - we are in the simplest case possible. A number such as "123,456" has two periods - the thousands period and the 'units' period. We do not name the unit period in common speech - it is assumed (we do not say "one hundred twenty three thousand four hundred fifty six unit" - we would say "one hundred twenty three thousand four hundred fifty six").
Armed with this, let's throw down some code. In a blank Excel sheet, press Alt-F11. This should open up the Macro screen. On the new sheet you just made, right click and Insert -> Module.

Public Function DBLTOTEXT(num As Variant, Optional ord As Integer = 0) As String
    Dim majscale() As String, tens() As String, ones() As String
    ones = Split(",one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen",",")
    tens = Split(",ten,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
    majscale = Split(",thousand,million,billion,trillion,quadrillion,quintillion,sextillion,septillion",",")
   
    Dim i As Variant, h As String, t As String, o As String
    Dim u, l As Variant, x As String, n As String

    i = num
   
    Select Case i
        Case 0 To 19
            o = ones(i)
        Case 20 To 99
            t = tens(Int(i / 10))
            o = DBLTOTEXT(i - Int(i / 10) * 10)
        Case 100 To 999
            h = ones(Int(i / 100)) & " hundred"
            t = DBLTOTEXT(i - Int(i / 100) * 100)
        Case Else
            u = Int(i / 1000)
            l = i - Int(i / 1000) * 1000
            x = DBLTOTEXT(u, ord + 1) & " " & DBLTOTEXT(l)
    End Select
    n = Trim(IIf(h = "", "", h & " ") & IIf(t = "", "", t & " ") & IIf(o = "", "", o & " "))
    If n <> "" Then n = n & " " & majscale(ord)
    DBLTOTEXT = Trim(IIf(x = "", "", x & " ") & n)
End Function

Placing this in a Macro in Excel will make it available to the spreadsheet. Close the Macro window and in a blank cell in Excel, type:

=DBLTOTEXT("1023")

If all is well, you should end up with the result of the function, "one thousand twenty three".

A bit of an aside, but ended up being the real question originally (leave it to me to solve a non-existent problem) was assistance in converting dollars and cents to verbiage. This additional function will do just that:

Public Function DOLLTOTEXT(dollars As Double)
    Dim i As Double, f As Double
    i = Int(dollars)
    f = Round(dollars - i, 2) * 100
    DOLLTOTEXT = DBLTOTEXT(i) & " dollars" & IIf(f > 0, " and " & DBLTOTEXT(f) & " cents", "")
End Function

In part 2, we'll begin dissecting the code here and figure out what's going on.