How To Type Amount In Words In Excel

Working with financial data in Excel often requires converting numerical amounts into words for clarity, official documentation, or presentation purposes. Whether you're preparing invoices, checks, or financial reports, knowing how to convert amounts into words in Excel can save you time and reduce errors. In this comprehensive guide, we'll explore various methods to type amounts in words in Excel, including formulas, custom functions, and add-ins, to help you handle this task efficiently and accurately.

Understanding the Need for Converting Numbers to Words in Excel

Converting numerical amounts into words in Excel is useful in many scenarios:

• Financial Statements: Making figures clear and unambiguous in reports or official documents.
• Cheque Printing: Ensuring correctness on checks, where amounts are typically written in words.
• Legal Documents: Including amounts in words to prevent tampering or misinterpretation.
• Data Validation and Clarity: Making data more understandable for users who prefer reading amounts in words.

While Excel doesn't have a built-in function for this task, there are multiple ways to achieve the conversion efficiently. Let's dive into the most effective methods.

Method 1: Using VBA (Visual Basic for Applications) to Convert Numbers to Words

One of the most flexible approaches is creating a custom VBA function that converts numbers into words. This method is ideal for users who frequently need this feature and are comfortable with VBA coding.

Step-by-Step Guide to Create a VBA Function for Number to Words Conversion

• Open the VBA Editor: Press ALT + F11 to open the VBA editor in Excel.
• Insert a New Module: In the VBA editor, go to Insert > Module.
• Paste the Conversion Code: Copy and paste the following VBA code into the module window:

Function NumberToWords(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    
    ' Convert MyNumber to string
    MyNumber = Trim(Str(MyNumber))
    
    ' Find decimal place
    DecimalPlace = InStr(MyNumber, ".")
    
    ' Extract cents
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If
    
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then
            Dollars = Temp & Place(Count) & Dollars
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    
    If Dollars = "" Then Dollars = "Zero"
    NumberToWords = Dollars & " Dollars"
    If Cents <> "" Then
        NumberToWords = NumberToWords & " and " & Cents & " Cents"
    End If
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, 2) <> "00" Then
        Result = Result & GetTens(Mid(MyNumber, 2, 2))
    End If
    GetHundreds = Result
End Function

Function GetTens(ByVal MyTens)
    Dim Result As String
    Dim T As Integer
    T = Val(MyTens)
    If T < 20 Then
        Result = GetDigit(T)
    Else
        Select Case T \ 10
            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 "
        End Select
        If T Mod 10 > 0 Then
            Result = Result & GetDigit(T Mod 10)
        End If
    End If
    GetTens = Result
End Function

Function GetDigit(ByVal MyDigit)
    Select Case MyDigit
        Case 0: GetDigit = ""
        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

Close the VBA Editor: Save your work and close the editor.

Use the Function in Excel: Now, in your worksheet, type =NumberToWords(A1) where A1 contains the amount you want to convert.

This VBA function converts numbers into words, including handling decimals for cents. Remember to save your Excel file as a macro-enabled workbook (*.xlsm) to retain the VBA code.

Method 2: Using Excel Formulas for Small Numbers

If you only need to convert small numbers (up to 999 or 9999), you can create nested formulas without VBA. Here's an example of how to convert numbers up to 999:

• Create a set of helper formulas that break down the number into hundreds, tens, and units.
• Use nested IF statements or SWITCH functions to assign words based on the digit values.

However, this method becomes complex for larger numbers and isn't practical for amounts in the thousands or millions. For such cases, VBA or add-ins are more suitable.

Method 3: Using Excel Add-ins or Third-Party Tools

Several third-party add-ins and tools can convert numbers into words seamlessly:

• Kutools for Excel: Offers a feature to convert numbers to words with a simple click.
• Online Converters: Use online tools to generate the text and paste it into Excel.
• Custom Add-ins: Some developers create custom Excel add-ins for number-to-word conversion.

When choosing add-ins, ensure they are trustworthy and compatible with your Excel version. Installing such tools can significantly streamline your workflow, especially if you frequently need this feature.

Method 4: Using Built-in Number Formatting for Display Purposes

While Excel doesn't convert numbers into words automatically, you can use custom formatting to display amounts in a way that mimics words for presentation purposes:

• Select the cell with the amount.
• Go to Format Cells (Right-click > Format Cells).
• Choose Custom and enter a format like: "$" #,##0.00 "and zero dollars".

This method doesn't produce actual words but can be useful for stylistic purposes in reports.

Best Practices for Converting Amounts to Words in Excel

• Always Verify Results: Especially when using custom VBA functions, double-check the output for accuracy.
• Handle Large Numbers Carefully: For amounts in the millions or higher, ensure your VBA code or add-in supports such scales.
• Use Save as Macro-Enabled Files: Remember to save your work in the correct format (*.xlsm) when using VBA.
• Secure Your VBA Code: If sharing your workbook, consider protecting your VBA modules to prevent unauthorized changes.
• Backup Your Data: Before implementing complex formulas or VBA scripts, ensure you have backups to avoid data loss.

Conclusion

Converting amounts into words in Excel is a valuable skill that enhances the professionalism and clarity of your financial documents. While Excel doesn't offer a built-in function for this purpose, multiple methods exist—most notably VBA scripting, third-party add-ins, and formula-based approaches for smaller numbers. By choosing the method that best fits your needs and comfort level, you can streamline your workflow and ensure accuracy in your financial reporting.

Whether you're creating invoices, checks, or detailed reports, mastering how to type amounts in words in Excel will save you time and help avoid errors. Experiment with VBA or explore reliable add-ins to find the solution that works best for your specific requirements. With these tools at your disposal, handling amounts in words becomes a straightforward and efficient task.

Disclaimer: Articles are written by Humans, AI or Both. Verify Important information.