This article covers how to make a formula to convert number to words in Excel. Since, Excel does not have prebuilt function to convert number to words, we have to create a custom or user defined function by using VBA (Visual Basics for Applications).
If you are new with this term “VBA in Excel”. You can watch the VBA tutorial for beginners from below link.
Steps of Creating Number to Words Function
This basically involves below steps.
- Get the required VBA script to create Number to Words function.
- Install VBA code into Module
- Test the Function
- Save Excel Workbook as Macro-Enabled Type
Let’s see each step in detail.
Step 1: Get the VBA Script to Convert Number to Words in Excel
Below is the required VBA code for Number to Words function. Select all the code given below, then copy it.
Function NumberToWords(ByVal MyNumber)
Dim UnitsArray As Variant
Dim TensArray As Variant
Dim TempStr As String
Dim DecimalPlace As Integer
Dim Count As Integer
Dim DecimalPart As String
Dim DecimalWords As String
Dim ResultStr As String
' Define arrays for units and tens
UnitsArray = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", _
"Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
TensArray = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
' Convert MyNumber to a string and trim extra spaces
MyNumber = Trim(CStr(MyNumber))
' Find position of decimal place, if any
DecimalPlace = InStr(MyNumber, ".")
' Convert cents (decimal part) to words
If DecimalPlace > 0 Then
DecimalPart = Mid(MyNumber, DecimalPlace + 1)
MyNumber = Left(MyNumber, DecimalPlace - 1)
Else
DecimalPart = ""
End If
' Handle zero
If MyNumber = "" Or MyNumber = "0" Then
ResultStr = "Zero"
Else
Count = 1
Do While MyNumber <> ""
Dim GroupStr As String
GroupStr = Right(MyNumber, 3)
If GroupStr <> "000" Then
TempStr = ConvertHundreds(GroupStr)
Select Case Count
Case 2
TempStr = TempStr & " Thousand "
Case 3
TempStr = TempStr & " Million "
Case 4
TempStr = TempStr & " Billion "
Case Else
TempStr = TempStr & " "
End Select
ResultStr = TempStr & ResultStr
End If
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
End If
NumberToWords = Application.Trim(ResultStr)
' Add decimal part to the words
If DecimalPart <> "" Then
DecimalWords = " point"
For i = 1 To Len(DecimalPart)
DecimalWords = DecimalWords & " " & UnitsArray(Val(Mid(DecimalPart, i, 1)))
Next i
NumberToWords = NumberToWords & DecimalWords
End If
End Function
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
Dim UnitsArray As Variant
Dim TensArray As Variant
UnitsArray = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", _
"Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
TensArray = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = UnitsArray(Val(Mid(MyNumber, 1, 1))) & " Hundred"
End If
' Convert the tens and units place.
If Val(Mid(MyNumber, 2, 2)) < 20 Then
If Val(Mid(MyNumber, 2, 2)) <> 0 Then
If Result <> "" Then
Result = Result & " " & UnitsArray(Val(Mid(MyNumber, 2, 2)))
Else
Result = UnitsArray(Val(Mid(MyNumber, 2, 2)))
End If
End If
Else
If Result <> "" Then
Result = Result & " " & TensArray(Val(Mid(MyNumber, 2, 1)))
Else
Result = TensArray(Val(Mid(MyNumber, 2, 1)))
End If
If Mid(MyNumber, 3, 1) <> "0" Then
Result = Result & "-" & UnitsArray(Val(Mid(MyNumber, 3, 1)))
End If
End If
ConvertHundreds = Result
End FunctionStep 2: Install VBA Code in VBA Module
After copying above code, go to Excel and follow below given steps.
- Open VBA by clicking on “Developer” tab, then “Visual Basics”.
- In VBA Window, click on “Insert” tab, then “Module”.
- In the module page, paste the copied VBA code.
- Close the VBA Window.
If you have never used VBA before, you may not see “Developer” tab in Excel. To enable the developer tab, watch this video guide (this topic starts at 4 Min 48 Sec timestamp).
Step 3: Test the Function
To test the function, write a number in any cell in Excel. In next cell, press =, then type “Number”. You should see the function “NumbertoWords”. Double Click on the function. Click on the cell that has number. Close bracket and hit enter. The number should get converted into words format.
Step 4: Save the Excel workbook as Macro-Enabled File Type
If you want to save the NumbertoWords function in your workbook and reuse it later, you have to save it as macro enabled file type. For this, follow these steps.
- Click on the “File” tab.
- Click on “Save As”.
- In the file type dropdown, choose Excel Macro-Enabled Workbook (*.xlsm).
- Select the location to save the file.
- Click on the “Save” button.
This will create a new file with macro enabled type having file extension of (.xlsm). You have to open this file to re-use the NumbertoWords function.
Spellnumber Function for Currency Format
If you are looking for a function to convert numbers to currency format. For example, 123.65 = One Hundred Twenty-Three US Dollars and Sixty-Five Cents, you have to use a different VBA code following the same method explained above. If you need to create Spellnumber function for currency, watch below video tutorial.
