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.

Tips for Converting Number in Words in Excel

• Make sure to save your work before opening the VBA editor.
• 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.

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.
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!