How to Convert Amount in Words in Excel: A Step-by-Step Guide

Converting amounts into words in Excel can be a bit tricky, but it’s totally doable with the right steps. By using a combination of Excel functions and VBA (Visual Basic for Applications) code, you can easily translate numerical values into written text. This guide will walk you through the process, making it simple and straightforward.

How to Convert Amount in Words in Excel

In this tutorial, you’ll learn how to convert numerical amounts into words in Excel. This involves using a VBA script that will perform the conversion. Follow these steps closely to get your Excel sheet working like a charm.

Step 1: Open the Excel file

First things first, open the Excel file where you want to convert amounts into words.

Make sure you’ve saved a backup copy of your file just in case anything goes wrong.

Step 2: Open the VBA Editor

To access the VBA editor, press Alt + F11 on your keyboard.

This will open the Visual Basic for Applications window where you can write or paste the required code.

Step 3: Insert a New Module

In the VBA editor, go to Insert > Module to create a new module.

The new module is where you will paste the VBA code needed for the conversion.

Step 4: Copy and Paste the VBA Code

Copy the following VBA code and paste it into the new module:

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Temp 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 extra spaces.
    MyNumber = Trim(CStr(MyNumber))

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

    ' Convert integer part of number to text.
    If DecimalPlace > 0 Then
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If

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

    NumberToWords = Application.Trim(Units)
End Function

Private Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)

    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1)  "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If

    ' Convert the tens and ones place.
    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

Private 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

Private 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

This code defines the function that turns numbers into words.

Step 5: Save and Close the VBA Editor

Save your work by clicking File > Save, then close the VBA editor.

This will keep your changes and bring you back to your Excel worksheet.

Step 6: Use the Function in Your Worksheet

Go back to your Excel worksheet and use the new function. For example, type =NumberToWords(A1) in a cell where A1 contains the number you want to convert.

The function will now convert the number in cell A1 to words.

After following these steps, your Excel sheet will be able to convert any number into words, making your data much easier to read and understand.

Tips for Converting Amount in Words in Excel

• Backup Your Work: Always save a copy of your Excel file before making changes, especially when using VBA.
• Test the Function: Try the new function on a small range of cells first to ensure it works correctly.
• Use Clear Labels: When applying the function, use clear headings or labels to understand which cells are being converted.
• Keep It Simple: If you’re new to VBA, stick closely to the tutorial and avoid making unnecessary changes to the code.
• Update Regularly: Keep your Excel and VBA environment updated to avoid compatibility issues.

Frequently Asked Questions

Does this work in all versions of Excel?

Yes, but the steps might slightly vary in different versions. It’s best to follow the guide for the version you’re using.

Can I use this function without VBA?

Unfortunately, no. Excel doesn’t have a built-in function for converting numbers to words, so VBA is necessary.

Is it possible to convert decimal numbers?

The provided script focuses on whole numbers. You might need to modify the VBA code for handling decimals.

Can I share my Excel file with others?

Yes, but ensure they enable macros when they open the file to use the conversion function.

What if I receive an error?

Double-check the VBA code for any mistakes. Errors often occur due to typos or missing parts of the code.

Summary

1. Open the Excel file.
2. Open the VBA Editor.
3. Insert a new module.
4. Copy and paste the VBA code.
5. Save and close the VBA Editor.
6. Use the function in your worksheet.

Conclusion

Congrats! You’ve successfully learned how to convert amounts in words in Excel. This skill not only makes your data more readable but also adds a professional touch to your spreadsheets. Remember, the key to mastering Excel is practice and patience. If you want to dive deeper, explore more VBA functions or look into advanced Excel courses. This simple trick can be a game-changer, especially when dealing with financial reports or complex data sets. So go ahead, give it a shot, and see how it transforms your workflow!