Free Practice Workbook
Converting number to words in Microsoft Excel is useful for creating checks, invoices, and other financial documents. While Excel does not have a built-in function to convert numbers to words, there are several methods you can use to achieve this, including creating a custom VBA (Visual Basic for Applications) function. Below, we will walk you through each of these methods in detail.
Table of Contents
How to Convert Number to Words
Creating a custom VBA function is a common way to convert number to words in Excel. Here’s how you can do it:
STEP 1: Click the Visual Basic option in the Developer tab.
STEP 2: Click on Insert in the menu, then select Module. This will create a new module.
STEP 3: Copy and paste the following VBA code into the module:
Function NumberToWords(ByVal MyNumber)
Dim Units As String
Dim SubUnits As String
Dim TempStr As String
Dim DecimalPlace As Integer
Dim Count As Integer
Dim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to string and trim white space
MyNumber = Trim(CStr(MyNumber))
' Find Position of decimal place (if any) in MyNumber
DecimalPlace = InStr(MyNumber, ".")
' Convert SubUnits and set MyNumber to Units amount
If DecimalPlace > 0 Then
SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
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
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
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
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
f
STEP 4: Press Ctrl + S to save your workbook as a macro-enabled workbook (.xlsm).
STEP 5: Close the VBA editor by clicking the X in the top-right corner or by pressing Alt + Q.
STEP 6: In your Excel worksheet, you can now use the NumberToWords function just like any other Excel function. For example, if you want to convert the number in cell A2 to words, you would enter the following formula:
=NumberToWords(A2)
Conclusion
Converting numbers to words in Excel can be easily accomplished using VBA code. Each method has its advantages, and the best choice will depend on your specific needs and familiarity with Excel’s advanced features. By following the steps outlined above, you can enhance your Excel documents with clear, readable text representations of numerical values.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
