ExcelInsider Team
Sometimes it can be hard to interpret the numeric data quickly. Here, converting numbers to words helps to make them more readable and easy to understand. That’s especially for invoices, checks, or formal reports.
However, Excel doesn’t have a built-in function for this. But you can convert a number to words in Excel very easily using formulas & VBA (Visual Basic for Applications). In today’s guide, we will show you both a simple formula and an advanced VBA method to convert numbers to words accurately in Excel.
➤ Excel doesn’t have a built-in feature to convert numbers into words. You’ll need VBA code for that.
➤ The NumToWords function: It turns numbers (like 123) into full text (like One Hundred Twenty Three).
➤ The AmountToWords function: It converts numeric currency values into words. For instance, $123 becomes One Hundred Twenty Three Dollars.
In this article, we will learn to convert numbers to words in Excel with the help of VBA code.
What Does It Mean To Convert The Number To Words In Excel?
In an Excel worksheet, converting the number into words means changing the numeric values into a readable text format. For instance, the numeric value “123” to text format “One Hundred Twenty Three”.
It is especially useful for invoices, cheques, and reports where you generally need the amount written in words. Typically, Excel does not offer this feature by default. However, you can accomplish it very easily with VBA code.
Convert The Number To Words Using a Formula
In the following dataset, we will convert the numbers in Column C to words using LEFT, MID, TEXT, and CHOOSE functions in Excel. This method is useful when you want to avoid the applications of VBA coding in your Excel file and need a lightweight solution.
Steps:
➤ Insert the following formula in the D2 cell.
=CHOOSE(LEFT(TEXT(C2,”000000000.00″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”) &IF(–LEFT(TEXT(C2,”000000000.00″))=0,,IF(AND(–MID(TEXT(C2,”000000000.00″),2,1)=0,–MID(TEXT(C2,”000000000.00″),3,1)=0),” Hundred”,” Hundred and “)) &CHOOSE(MID(TEXT(C2,”000000000.00”),2,1)+1,,,”Twenty “,”Thirty “,”Forty “,”Fifty “,”Sixty “,”Seventy “,”Eighty “,”Ninety “) &IF(–MID(TEXT(C2,”000000000.00″),2,1)<>1,CHOOSE(MID(TEXT(C2,”000000000.00″),3,1)+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”), CHOOSE(MID(TEXT(C2,”000000000.00″),3,1)+1,”Ten”,”Eleven”,”Twelve”,”Thirteen”,”Fourteen”,”Fifteen”,”Sixteen”,”Seventeen”,”Eighteen”,”Nineteen”)) &IF((–LEFT(TEXT(C2,”000000000.00″))+MID(TEXT(C2,”000000000.00″),2,1)+MID(TEXT(C2,”000000000.00″),3,1))=0,,IF(AND((–MID(TEXT(C2,”000000000.00″),4,1)+MID(TEXT(C2,”000000000.00″),5,1)+MID(TEXT(C2,”000000000.00″),6,1)+MID(TEXT(C2,”000000000.00″),7,1))=0,(–MID(TEXT(C2,”000000000.00″),8,1)+RIGHT(TEXT(C2,”000000000.00″)))>0),” Million and “,” Million “)) &CHOOSE(MID(TEXT(C2,”000000000.00″),4,1)+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”) &IF(–MID(TEXT(C2,”000000000.00″),4,1)=0,,IF(AND(–MID(TEXT(C2,”000000000.00″),5,1)=0,–MID(TEXT(C2,”000000000.00″),6,1)=0),” Hundred”,” Hundred and”)) &CHOOSE(MID(TEXT(C2,”000000000.00″),5,1)+1,,,” Twenty”,” Thirty”,” Forty”,” Fifty”,” Sixty”,” Seventy”,” Eighty”,” Ninety”) &IF(–MID(TEXT(C2,”000000000.00″),5,1)<>1,CHOOSE(MID(TEXT(C2,”000000000.00″),6,1)+1,,” One”,” Two”,” Three”,” Four”,” Five”,” Six”,” Seven”,” Eight”,” Nine”),CHOOSE(MID(TEXT(C2,”000000000.00″),6,1)+1,” Ten”,” Eleven”,” Twelve”,” Thirteen”,” Fourteen”,” Fifteen”,” Sixteen”,” Seventeen”,” Eighteen”,” Nineteen”)) &IF((–MID(TEXT(C2,”000000000.00″),4,1)+MID(TEXT(C2,”000000000.00″),5,1)+MID(TEXT(C2,”000000000.00″),6,1))=0,,IF(OR((–MID(TEXT(C2,”000000000.00″),7,1)+MID(TEXT(C2,”000000000.00″),8,1)+MID(TEXT(C2,”000000000.00″),9,1))=0,–MID(TEXT(C2,”000000000.00″),7,1)<>0),” Thousand “,” Thousand and “)) &CHOOSE(MID(TEXT(C2,”000000000.00″),7,1)+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”) &IF(–MID(TEXT(C2,”000000000.00″),7,1)=0,,IF(AND(–MID(TEXT(C2,”000000000.00″),8,1)=0,–MID(TEXT(C2,”000000000.00″),9,1)=0),” Hundred “,” Hundred and “))& CHOOSE(MID(TEXT(C2,”000000000.00”),8,1)+1,,,”Twenty “,”Thirty “,”Forty “,”Fifty “,”Sixty “,”Seventy “,”Eighty “,”Ninety “) &IF(–MID(TEXT(C2,”000000000.00″),8,1)<>1,CHOOSE(MID(TEXT(C2,”000000000.00″),9,1)+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”),CHOOSE(MID(TEXT(C2,”000000000.00″),9,1)+1,”Ten”,”Eleven”,”Twelve”,”Thirteen”,”Fourteen”,”Fifteen”,”Sixteen”,”Seventeen”,”Eighteen”,”Nineteen”))
➤ Press Enter.
➤ Drag the Fill Handle tool to cell D11 to show all the outputs in Column D.
Here is a short breakdown of the formula:
➥ TEXT(C2, "000000000.00"): Converts the number to exactly 9 digits before the decimal, so it works for numbers up to 999,999,999 (under 1 billion)
➥ LEFT, MID, RIGHT: Pull out digits based on position.
➥CHOOSE: Select the right word for each number. For instance, 1 into "One"
➥ Handles Hundreds, Tens, Teens, and Units: Builds full number to word phrases like “One Hundred and Twenty Three”.
➥ Adds segment labels: Includes “Million”, “Thousand”, and “Hundred” where needed.
➥ Follows grammar rules: Uses “and” correctly.
Convert Number To Words with VBA User-Defined Function
VBA code can be the most effective solution when you want to convert large numbers into words efficiently. It creates a custom function that automatically converts any numeric values in words.
In the following datasheet, we have transaction amounts from different Entry IDs. We are going to convert each number to words using VBA.
You can easily convert the number into words using VBA functions. Follow the steps below to set it up.
➤ Open VBA editor pressing Alt + F11 on your keyboard.
➤ Once the VBA editor is opened, select Insert >> then Module.
➤ Now, in the new module, write the following VBA code to convert a number to words. You can also just copy the code and paste it into the module.
Function NumToWords(ByVal MyNumber As Variant, Optional isProper As Boolean = False) As String
Dim Units As String, SubUnits As String, TempStr As String
Dim DecimalPlace As Integer, Count As Integer
Dim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Check if the input is empty or not a number
If IsEmpty(MyNumber) Or Not IsNumeric(MyNumber) Then
NumToWords = ""
Exit Function
End If
' Convert to string to preserve decimal digits
MyNumber = Trim(CStr(MyNumber))
' Handle Zero
If Val(MyNumber) = 0 Then
NumToWords = "Zero"
Exit Function
End If
' Find decimal position
DecimalPlace = InStr(MyNumber, ".")
' Process decimal part
If DecimalPlace > 0 Then
SubUnits = GetDecimalWords(Mid(MyNumber, DecimalPlace + 1), isProper)
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
' Process integer part
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
' Combine integer and decimal part
If SubUnits = "" Then
NumToWords = Application.Trim(Units)
Else
NumToWords = Application.Trim(Units & " Point " & SubUnits)
End If
End Function
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
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 = ""
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"
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 "
End Select
Result = Result & GetDigit(Right(TensText, 1))
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
Function GetDecimalWords(DecimalPart As String, isProper As Boolean) As String
Dim i As Integer, DecWord As String
If isProper Then
' e.g., 45.67 -> Forty Five Point Six Seven
For i = 1 To Len(DecimalPart)
If i > 1 Then DecWord = DecWord & " "
DecWord = DecWord & GetDigit(Mid(DecimalPart, i, 1))
Next i
Else
' e.g., 45.67 -> Forty Five Point Sixty Seven
DecimalPart = Left(DecimalPart & "00", 2)
DecWord = GetTens(DecimalPart)
End If
GetDecimalWords = DecWord
End Function
➤ After inserting the code, close the VBA module and return to the Excel worksheet.
➤ Now you can use the function like a built-in one in Excel.
➤ In cell D2, write the following custom function that is built by VBA:
=NumToWords(C2).
➤ Press Enter, and you will see the results. For input: =NumberToWords(C2), we got the output: One Hundred Fifty.
With this NumberToWords formula, you can convert any numeric value to words.
Now, drag the Fill Handle tool to cell D11 to show all the outputs in Column D.
(full output to be shown in column D)
Convert Decimal Numbers To Words
Now, if you want to change the decimal number to words, this code can also handle both the integer and decimal parts.
You can also control how the decimal portion is read using an optional argument in the function. Here it is:
- True: Converts each digit in the decimal part individually.
For instance, 25.56 becomes “Twenty Five Point Five Six”.
- False: Converts the decimal part to a two-digit number.
For instance, 25.56 becomes “Twenty Five Point Fifty Six”.
In the following datasheet, we’re going to convert decimal numbers to words using the VBA code.
➤ Open the VBA Editor, insert the code in the module, and return to the Excel worksheet.
➤ Select the output cell D2 and write down the following formula:
=NumToWords(C2, True).
➤ Press Enter, and you’ll see that the decimal value has been converted to words in the image shown below.
➤ Drag the Fill Handle tool to show all the outputs in Column D.
Convert Currency Values To Words In Excel
You can convert numbers into words along with currency, with a custom VBA function. Follow the steps below to do so.
➤ Open the VBA editor by pressing Alt + F11 on your keyboard.
➤ Select Insert >> Module.
➤ In the new module, type the following AmountToWords VBA code.
Function AmountToWords(ByVal MyNumber As Variant, ByVal strCurrency As String, ByVal strUnits As String) As String
Dim IntegerPart As String, DecimalPart As String
Dim DecimalPlace As Integer
' Check for valid numeric input
If IsEmpty(MyNumber) Or Not IsNumeric(MyNumber) Then
AmountToWords = ""
Exit Function
End If
' Convert to string and clean
MyNumber = Trim(CStr(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
' Split into integer and decimal parts
If DecimalPlace > 0 Then
IntegerPart = Left(MyNumber, DecimalPlace - 1)
DecimalPart = Mid(MyNumber, DecimalPlace + 1)
Else
IntegerPart = MyNumber
DecimalPart = ""
End If
' Convert integer part
If CLng(IntegerPart) > 0 Then
AmountToWords = NumToWords(CLng(IntegerPart)) & " " & strCurrency
End If
' Convert decimal part (up to two digits only)
If DecimalPart <> "" Then
DecimalPart = Left(DecimalPart & "00", 2)
If CInt(DecimalPart) > 0 Then
If AmountToWords <> "" Then AmountToWords = AmountToWords & " and "
AmountToWords = AmountToWords & NumToWords(CInt(DecimalPart)) & " " & strUnits
End If
End If
End Function
' Helper Function: Converts numbers to words (up to 9999 for demo, can be expanded)
Function NumToWords(ByVal n As Long) As String
Dim ones, teens, tens
ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", _
"Sixteen", "Seventeen", "Eighteen", "Nineteen")
tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Dim word As String
If n = 0 Then
NumToWords = "Zero"
Exit Function
End If
If n >= 1000 Then
word = word & ones(n \ 1000) & " Thousand "
n = n Mod 1000
End If
If n >= 100 Then
word = word & ones(n \ 100) & " Hundred "
n = n Mod 100
End If
If n >= 20 Then
word = word & tens(n \ 10) & " "
n = n Mod 10
ElseIf n >= 10 Then
word = word & teens(n - 10) & " "
n = 0
End If
If n > 0 Then
word = word & ones(n) & " "
End If
NumToWords = Trim(word)
End Function➤ After you’ve added the AmountToWords function to your VBA editor, close the Macro Editor.
➤ In cell D2, write down the following custom function built by VBA:
=AmountToWords(C2, “Dollar”, “Cent”).
➤ Press Enter to get the amount.
➤ The AmountToWord VBA function easily converts many numeric values of different currencies into words.
The following are some examples of AmountToWord VBA functions for different currencies.
- Dollars (USD): =AmountToWords(Cell, “Dollars”, “Cents”)
- Euros (EUR): =AmountToWords(Cell, “Euros”, “Cents”)
- Canadian Dollars (CAD): =AmountToWords(Cell, “Canadian Dollars”, “Cents”)
- Australian Dollars (AUD): =AmountToWords(Cell, “Australian Dollars”, “Cents”)
- Dirhams (AED): =AmountToWords(Cell, “Dirhams”, “Fils”)
- British Pounds (GBP): =AmountToWords(Cell, “Pounds”, “Pence”)
- Indian Rupees (INR): =AmountToWords(Cell, “Rupees”, “Paise”)
Note:
Cell is the cell number containing the numeric value of the currencies you are going to convert.
➤ To save your updated workbook, press Ctrl + S >> Click ‘No’ in the new dialog box.
➤ In the “Save as type” field, choose “Excel Macro-Enabled Workbook“.
Frequently Asked Questions
Where is the conversion to a number in Excel?
If you see a green triangle in a cell,
➤ Select the cells
➤ Click the warning icon.
➤ Choose Convert to Number
How to convert a formula to a number in Excel?
➤ Select the cell containing the formula
➤ Press F2 >> Then F9
➤ Press Enter to finalize.
Why is Excel converting my numbers to text?
It could be that the numbers are stored as text. Numbers formatted as text won’t be able to calculate or sort properly.
What is the shortcut key for converting to a number in Excel?
➤ Select the cells with numbers stored as text.
➤ Press Alt + A + E (opens Text to Columns)
➤ Click Finish
Wrapping Up
In this quick tutorial, we have learnt how to convert a number to words in Excel using a custom VBA function. Feel free to download the practice worksheet file and let us know how it simplifies your Excel tasks.
