Converting Numeric Values to Words in Excel Using VBA

Technical Background and Problem Analysis

In data processing and financial reporting, there is often a need to convert numeric values in Excel into their corresponding English word forms, such as turning 123.45 into "One hundred and twenty-three point four five". However, Microsoft Excel 2007 and later versions do not provide built-in functions for this conversion. This stems from the complex linguistic rules involved, including digit grouping, special number names (e.g., 11-19), and the use of connectors like "and".

Overview of the VBA Macro Solution

Using Visual Basic for Applications (VBA), we can create custom functions to extend Excel's capabilities. The core idea is to split the number into groups of three digits (corresponding to millions, thousands, and units), process the hundreds and tens/units within each group separately, and then concatenate the results. This approach avoids the complexity of direct string manipulation and enhances code maintainability.

Detailed Code Implementation

First, initialize two global arrays: Numbers stores English words for 0-19, and Tens stores words for tens from 20-90. This forms the foundational data for conversion.

Option Explicit
Public Numbers As Variant, Tens As Variant

Sub SetNums()
    Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

The main function WordNum takes a Double parameter and returns a string. It first checks the value range, returning an error if it exceeds 999,999,999. Then, it calls SetNums to initialize the arrays.

Function WordNum(MyNumber As Double) As String
    Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
    Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
    If Abs(MyNumber) > 999999999 Then
        WordNum = "Value too large"
        Exit Function
    End If
    SetNums

Format the integer part as a 9-digit string to facilitate splitting into groups of three. Use the ValNo array to store the value of each group.

    NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
    ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

Loop from the highest group (millions) to the lowest (units). For each group, extract the last two digits and convert them using the GetTens function, then process the hundreds place.

    For n = 3 To 1 Step -1
        StrNo = Format(ValNo(n), "000")
        If ValNo(n) > 0 Then
            Temp1 = GetTens(Val(Right(StrNo, 2)))
            If Left(StrNo, 1) <> "0" Then
                Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
                If Temp1 <> "" Then Temp2 = Temp2 & " and "
            Else
                Temp2 = ""
            End If

Add thousand or million suffixes based on the group position and concatenate the results.

            If n = 3 Then
                If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
                WordNum = Trim(Temp2 & Temp1)
            End If
            If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
            If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
        End If
    Next n

Handle the decimal part: locate the decimal point and convert each digit to its corresponding word.

    NumStr = Trim(Str(Abs(MyNumber)))
    DecimalPosition = InStr(NumStr, ".")
    Numbers(0) = "Zero"
    If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
        Temp1 = " point"
        For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
        WordNum = WordNum & Temp1
    End If

If the integer part is zero, add a "Zero" prefix.

    If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
        WordNum = "Zero" & WordNum
    End If
End Function

The GetTens helper function processes numbers from 0-99: for values up to 19, it looks up the array; otherwise, it splits the tens and units.

Function GetTens(TensNum As Integer) As String
    If TensNum <= 19 Then
        GetTens = Numbers(TensNum)
    Else
        Dim MyNo As String
        MyNo = Format(TensNum, "00")
        GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
    End If
End Function

Deployment and Usage

In Excel, press Alt+F11 to open the VBA editor, insert a module, and paste the code. Save it as an "Excel 97-2003 Add-In (*.xla)" to create a loadable add-in, making it available across all workbooks. To use, enter =WordNum(A1) in a cell, where A1 is the reference to the numeric cell.

Extensions and Optimizations

This solution can be extended to support larger numbers (e.g., billions) or different languages. Optimization directions include adding error handling (e.g., for non-numeric inputs), improving performance (e.g., caching arrays), and supporting negative numbers (by adding a "minus" prefix). The modular design facilitates easy integration of these features.