Convert Numbers to Words in Excel – Step-by-Step Guide

Introduction

Have you ever needed to convert a number to words in Excel? Whether you’re preparing financial documents, invoices, or reports, converting numbers to words can add clarity and professionalism to your spreadsheets. In this blog post, we’ll explore different methods to achieve this in Excel.

Why Convert Numbers to Words in Excel?

When you convert numbers to words in Excel, it helps in financial statements, invoices, and legal documents.

• Financial Documents: Ensuring clarity in financial statements and invoices.
• Legal Documents: Adding a layer of precision in legal contracts.
• Data Presentation: Enhancing the readability of data in reports and presentations.

Method 1: Convert Numbers to Words in Excel Using VBA

One of the most effective ways to convert numbers to words in Excel is by using VBA. Here’s a step-by-step guide:

1. Open Excel and Press Alt + F11 – This opens the VBA editor.
2. Insert a New Module – Go to Insert > Module.
3. Copy and Paste the Following Code:

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Thousands As String
    Dim TempStr As String
    Dim DecimalPlace As Integer
    Dim Count As Integer

    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    ' Convert MyNumber to string and Trim white space
    MyNumber = Trim(CStr(MyNumber))

    ' Find Position of decimal place
    DecimalPlace = InStr(MyNumber, ".")

    ' Convert cents and set MyNumber to dollar amount
    If DecimalPlace > 0 Then
        TempStr = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        TempStr = GetHundreds(Right(MyNumber, 3)) & Place(Count) & TempStr
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    NumberToWords = Application.Trim(TempStr)
End Function

Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Function GetTens(TensText)
    Dim Result As String
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit(Right(TensText, 1))   ' Retrieve ones place.
    End If
    GetTens = Result
End Function

Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

4. Save and Close the VBA Editor.

5. Use the Function in Excel: In any cell, use the formula =NumberToWords(A1) where A1 is the cell containing the number you want to convert.

Method 2: Convert Numbers to Words in Excel Using Add-Ins

If you’re uncomfortable with VBA, you can use Excel add-ins offering number-to-word conversion. Some popular add-ins include:

• Number to Words: A powerful add-in to number-to-words conversion.

Method 3: Convert Numbers to Words in Excel Using Online Tools

There are also online tools available that can convert numbers to words. You can use these tools to generate the text and then copy it into your Excel sheet.

Conclusion

When you need to convert numbers to words in Excel, you can use VBA, add-ins, or online tools. This makes your documents more professional and readable. Whether you choose to use VBA, add-ins, or online tools, these methods will help you achieve the desired results. Try out these methods and see which one works best for you!