Converting Numbers to Words in Excel: A Comprehensive Guide

Converting numbers to words in Excel is a useful feature, especially for creating financial documents like invoices and checks. While Excel doesn’t have a built-in function for this, you can achieve it using VBA (Visual Basic for Applications). In this article, we’ll guide you through the process of creating a custom function in Excel to convert numbers to words.

Example: 1234.56 is One Thousand Two Hundred Thirty-Four and Fifty-Six Cents

Why Convert Numbers to Words?

  1. Check Writing: Helps ensure the amount is clearly understood.
  2. Invoices: Adds a professional touch to financial documents.
  3. Reports: Improves clarity and presentation.

Step-by-Step Instructions

Step 1: Open the VBA Editor

  1. Open your Excel workbook.
  2. Press ALT + F11 to open the VBA editor.

Step 2: Insert a New Module

  1. In the VBA editor, click Insert.
  2. Select Module.

Step 3: Add VBA Code to Convert Numbers to Words

Copy and paste the following VBA code into the module:

Function NumberToWords(ByVal Number As Double) As String
    Dim Units As Variant
    Dim Teens As Variant
    Dim Tens As Variant
    Dim Thousands As Variant
    Dim IntegerPart As Long
    Dim DecimalPart As Double
    Dim TempStr As String
    Dim DecimalStr As String
    Dim DecimalNumber As Long
    
    Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
    Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    Thousands = Array("", "Thousand", "Million", "Billion")
    
    ' Handle zero
    If Number = 0 Then
        NumberToWords = "Zero"
        Exit Function
    End If
    
    ' Split number into integer and decimal parts
    IntegerPart = Int(Number)
    DecimalPart = Round(Number - IntegerPart, 2)
    DecimalStr = ""
    
    If DecimalPart > 0 Then
        DecimalNumber = DecimalPart * 100
        DecimalStr = " and " & ConvertToWords(DecimalNumber, Units, Teens, Tens, Thousands) & " Cents"
    End If
    
    ' Convert integer part
    TempStr = ConvertToWords(IntegerPart, Units, Teens, Tens, Thousands)
    NumberToWords = TempStr & DecimalStr
End Function

Function ConvertToWords(ByVal Number As Long, Units As Variant, Teens As Variant, Tens As Variant, Thousands As Variant) As String
    Dim Result As String
    Dim TempStr As String
    Dim ThousandIndex As Integer
    Dim TempNumber As Long
    
    Result = ""
    ThousandIndex = 0
    
    Do While Number > 0
        TempNumber = Number Mod 1000
        If TempNumber > 0 Then
            TempStr = ConvertHundreds(TempNumber, Units, Teens, Tens)
            If ThousandIndex > 0 Then
                TempStr = TempStr & " " & Thousands(ThousandIndex)
            End If
            Result = TempStr & " " & Result
        End If
        ThousandIndex = ThousandIndex + 1
        Number = Number \ 1000
    Loop
    
    ConvertToWords = Trim(Result)
End Function

Function ConvertHundreds(ByVal Number As Long, Units As Variant, Teens As Variant, Tens As Variant) As String
    Dim Result As String
    Dim TempNumber As Long
    
    Result = ""
    TempNumber = Number \ 100
    If TempNumber > 0 Then
        Result = Units(TempNumber) & " Hundred "
        Number = Number Mod 100
    End If
    
    If Number >= 10 And Number < 20 Then
        Result = Result & Teens(Number - 10)
    Else
        TempNumber = Number \ 10
        If TempNumber > 0 Then
            Result = Result & Tens(TempNumber) & " "
            Number = Number Mod 10
        End If
        If Number > 0 Then
            Result = Result & Units(Number)
        End If
    End If
    
    ConvertHundreds = Trim(Result)
End Function

Step 4: Save and Close the VBA Editor

  1. Click File > Close and Return to Microsoft Excel.
  2. Save your workbook as a macro-enabled workbook (.xlsm).

Step 5: Use the Function in Excel

  1. In any cell, type the custom function =NumberToWords(CellReference).
  2. For example, if you enter =NumberToWords(1234.56) in a cell, it will display “One Thousand Two Hundred Thirty-Four and Fifty-Six Cents”.

Conclusion

Using VBA to convert numbers to words in Excel adds significant value to your spreadsheets, making them more readable and professional. By following this guide, you can easily implement this functionality and enhance your Excel documents.

Related Articles: