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 String
    Dim Teens As String
    Dim Tens As String
    Dim Thousands As String
    Dim IntegerPart As Variant
    Dim DecimalPart As Variant
    Dim TempStr As String
    Dim DecimalStr As String
    Dim DecimalNumber As Integer
    
    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")
    
    ' Split number into integer and decimal parts
    IntegerPart = Int(Number)
    DecimalPart = Number - IntegerPart
    
    If DecimalPart > 0 Then
        DecimalNumber = Round(DecimalPart * 100, 0)
        DecimalStr = " and " & NumberToWords(IntegerPart) & " " & NumberToWords(DecimalNumber) & " Cents"
    End If
    
    ' Convert integer part
    NumberToWords = ConvertToWords(IntegerPart, Units, Teens, Tens, Thousands) & 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: