How to Convert Number in Words in Excel: Step-by-Step Guide

When working with Excel, sometimes you need to convert numbers to words—whether for writing checks, creating invoices, or just to make your data more readable. This process involves using a combination of Excel functions and Visual Basic for Applications (VBA) scripting.

How to Convert Number in Words in Excel

In this tutorial, we’ll dive into how to convert numbers into words in Excel using a VBA script. This script will take a numerical value and translate it into English words, making it much easier to read. Here are the steps to accomplish this task.

Step 1: Open Excel and Access the Developer Tab

First, open your Excel workbook and navigate to the Developer tab.

If you don’t see the Developer tab, you’ll need to enable it. Go to File > Options > Customize Ribbon. Then, check the box next to "Developer" and click OK.

Step 2: Insert a New Module in the VBA Editor

Navigate to the Developer tab, and click on "Visual Basic" to open the VBA editor.

In the VBA editor, insert a new module by going to Insert > Module. A new module window will appear where you can write your script.

Step 3: Copy and Paste the VBA Script

Copy the following VBA script and paste it into the module window:

Function NumberToWords(ByVal MyNumber)

    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Thousands As String

    Dim place(1 To 5) As String
    place(1) = ""
    place(2) = " Thousand "
    place(3) = " Million "
    place(4) = " Billion "
    place(5) = " Trillion "

    MyNumber = Trim(CStr(MyNumber))

    Dim DecimalPlace As Integer
    DecimalPlace = InStr(MyNumber, ".")

    If DecimalPlace > 0 Then
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If

    Dim X As Integer
    Dim TempStr As String
    Dim Count As Integer

    Count = 1
    Do While MyNumber  ""
        TempStr = GetHundreds(Right(MyNumber, 3))
        If TempStr  "" Then Units = TempStr & 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 Units As String
    Dim Num As Integer

    Units = ""
    Num = Val(MyNumber)

    If Num = 0 Then Exit Function

    MyNumber = Right("000" & MyNumber, 3)

    If Mid(MyNumber, 1, 1)  "0" Then
        Units = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If

    If Mid(MyNumber, 2, 1)  "0" Then
        Units = Units & GetTens(Mid(MyNumber, 2))
    Else
        Units = Units & GetDigit(Mid(MyNumber, 3))
    End If

    GetHundreds = Units
End Function

Private Function GetTens(TensText)

    Dim Result As String
    Result = ""

    If Val(Left(TensText, 1)) = 1 Then
        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
        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))
    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 script defines a function called NumberToWords that you can use in your Excel workbook.

Step 4: Save Your VBA Project

Save your VBA project by clicking File > Save. Close the VBA editor.

You can now use the NumberToWords function just like any other Excel function.

Step 5: Use the Function in Your Excel Workbook

To convert a number to words, simply type =NumberToWords(A1) into a cell, where A1 is the cell containing the number.

Excel will return the number in words in the cell where you entered the formula.

After completing these steps, your numbers will be converted into words, making your spreadsheet more user-friendly and readable.

Tips for Converting Number in Words in Excel

• Make sure to save your work before opening the VBA editor.
• Use descriptive cell names or comments to keep your spreadsheet organized.
• You can modify the VBA script to handle different languages or formats.
• Test the function with various numbers to ensure it works correctly.
• If you encounter any issues, check for syntax errors in the VBA script.

Frequently Asked Questions

What if the Developer tab is not visible?

Go to File > Options > Customize Ribbon and check the box next to "Developer."

Can I use this function in any version of Excel?

Yes, but VBA support must be enabled.

What if I receive an error message?

Double-check the script for syntax errors and ensure all parentheses and quotation marks are closed properly.

Can I convert large numbers?

Yes, the script can handle large numbers up to trillions.

Will this script work for decimal numbers?

The provided script does not handle decimal numbers but can be modified to do so.

Summary

1. Open Excel and access the Developer tab.
2. Insert a new module in the VBA editor.
3. Copy and paste the VBA script.
4. Save your VBA project.
5. Use the function in your workbook.

Conclusion

Converting numbers to words in Excel might seem like a complex task, but with the help of VBA scripting, it becomes straightforward. This tutorial walked you through the steps to create a custom Excel function that translates numbers into English words. Whether you’re preparing financial documents or just want to make your data more readable, this function is incredibly useful.

Feel free to experiment with the script to better suit your needs. You can modify it to handle different languages or formats, making it a versatile tool in your Excel arsenal. If you found this guide helpful, explore more VBA functions to further enhance your Excel skills.

Remember, Excel is a powerful tool, and with a bit of creativity and coding, you can achieve almost anything. Now go ahead, try converting numbers to words in your Excel workbook, and see how it simplifies your tasks!