Converting numbers to words in Excel is a common task, but Excel doesn’t have the built-in Function for this. However, it can be easily accomplished with the help of Visual Basic for Applications (VBA). In this blog post, we will walk you through the steps of creating a user-defined function that converts a numeric value to words.
We created Simple Excel Formula to Convert Numbers to Words. You can simply Copy and Paste the VBA Function and use it in both Excel Formulas and VBA Procedures. You can Pass any Number to the Function to Spell Number in Words.
Steps to Convert Numbers to Words
Here are the simple steps for Converting numbers to words in Excel can be done by creating a custom function in Visual Basic for Applications (VBA).
- Step 1: Open VBA Editor: To start the process, you will first need to open the VBA editor. This can be accomplished by pressing Alt + F11 on your keyboard while in Excel.
- Step 2: Insert a New Module: Once the VBA editor is open, you should navigate to Insert > Module in the menu. This will create a new module where you can write your code.
- Step 3: Write the Code/Copy Our NumToWords VBA Code: Within this new module, you will need to input the code that will create your custom function for converting numbers to words. You can either write your own code or use the VBA code provided in the previous section of this post.
- Step 4: Use the Function: After the code is implemented, you can now use the new function just like any other built-in function in Excel. Simply return to your Excel sheet and in any cell, write =NumToWords(CELL). You should replace CELL with the cell number that contains the numeric value you wish to convert. For example, =NumToWords(A1) will convert the number in cell A1 to words.
With these steps, you can easily convert numbers to words in Excel, enhancing your data analysis and reporting capabilities.
VBA to Convert Numbers to Words
Here is the easy to use Excel VBA Function to convert number to words. You can copy the below code and paste in your code module.
Option Explicit 'Main Function to Convert Number to Words Function NumToWords(ByVal MyNumber) Dim Units As String Dim SubUnits As String Dim TempStr As String Dim DecimalPlace As Integer Dim Count As Integer ReDim 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(Str(MyNumber)) ' Find position of decimal place 0 if none. 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 NumToWords = Application.Trim(Units & SubUnits) End Function ' Converts a number from 100-999 into text 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 ' Converts a number from 10 to 99 into text. 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 ' Converts a number from 1 to 9 into text. 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
Now that you have defined the function, you can use it just like any other Excel function. You can use it as Excel formula in a Cell or you can assign to a variable in VBA.
Excel Formula to Convert Numbers to Words
The NumToWords
function, created in Excel VBA, converts numbers into their word equivalent. This is especially handy for writing checks, generating invoices, or creating certain reports. The example table provided uses this function. It has three columns: ‘Number’, ‘Formula’, and ‘Result’. ‘Number’ holds the numerical values. ‘Formula’ applies the NumToWords
function to the numbers in Column A. ‘Result’ shows the output of the function – the verbal representation of the numbers.
To use this function, remember to first implement NumToWords
in your Excel VBA as outlined in the prior steps.
=NumToWords(A1)
Number(A) | Formula(B) | Result(C) |
---|---|---|
1 | =NumToWords(A2) |
One |
23 | =NumToWords(A3) |
Twenty Three |
456 | =NumToWords(A4) |
Four Hundred Fifty Six |
7890 | =NumToWords(A5) |
Seven Thousand Eight Hundred Ninety |
12345 | =NumToWords(A6) |
Twelve Thousand Three Hundred Forty Five |
Your number will be converted to words in the cell where you wrote the formula!
Numbers to Words – VBA Use Cases
The NumToWords
function in Excel VBA converts numeric values to words. To use it, you first open the VBA editor (Alt + F11
), create a new module, and input the function code. Once it’s implemented, you can use it within your VBA scripts.
=strNumberToWord=NumToWords(A1)
For example:
- Assign it to a variable:
- Dim result As String
result = NumToWords(123)
- Show the result in a message
MsgBox NumToWords(123)
- Enter the result in a cell,
Range("A1").Value = NumToWords(123)
which will enter the converted word form of the number into cell A1. This function extends Excel’s capabilities and offers more flexibility.
Remember, user-defined functions in Excel VBA are powerful and flexible tools that allow you to create new functionality, such as converting numbers to words. However, they are a bit more complex and require knowledge of VBA to create and debug.
Convert Numbers to Words in Excel -Download Example File
Here is the Example file with Excel formula to Convert Number to Words. You can see the Example of Function using in both Excel Formula and VBA.
Convert Amount to Words Using Excel Formula: Spell Numbers in Excel Without VBA
Have you ever needed to spell numbers in Excel for financial reports, invoices, or official documents? Converting numerical amounts to their word equivalents can enhance the professionalism and clarity of your spreadsheets. While VBA (Visual Basic for Applications) offers a robust solution, not everyone is comfortable with coding. Fortunately, you can achieve number to words in Excel formula without VBA using a comprehensive nested formula. In this blog post, we’ll walk you through an effective formula that transforms numbers into words seamlessly.
Why Convert Amount to Words in Excel?
Converting Amount to words is essential in various scenarios, such as:
- Financial Statements: Presenting amounts in words reduces the risk of tampering.
- Invoices and Bills: Enhances readability and professionalism.
- Legal Documents: Ensures clarity and prevents misunderstandings.
The Excel Formula to Spell Numbers
Below is a detailed Excel formula that converts numbers into words without the need for VBA. This formula handles numbers up to billions and accounts for units, tens, hundreds, thousands, millions, and billions.
Formula:
=IF(A1=0, "Zero", TRIM( IF(INT(A1/1000000000) > 0, LOOKUP(INT(A1/1000000000), {1,2,3,4,5,6,7,8,9}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Billion ", "") & IF(INT(MOD(A1,1000000000)/1000000) > 0, LOOKUP(INT(MOD(A1,1000000000)/1000000), {1,2,3,4,5,6,7,8,9}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Million ", "") & IF(INT(MOD(A1,1000000)/1000) > 0, IF(INT(MOD(A1,1000000)/1000 / 100) > 0, LOOKUP(INT(MOD(A1,1000000)/1000 / 100), {1,2,3,4,5,6,7,8,9}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Hundred ", "") & IF(MOD(INT(MOD(A1,1000000)/1000),100) < 20, LOOKUP(MOD(INT(MOD(A1,1000000)/1000),100), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}) & " ", LOOKUP(INT(MOD(INT(MOD(A1,1000000)/1000),100)/10), {2,3,4,5,6,7,8,9}, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}) & IF(MOD(INT(MOD(A1,1000000)/1000),10) >0, " " & LOOKUP(MOD(INT(MOD(A1,1000000)/1000),10), {1,2,3,4,5,6,7,8,9}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}), "") ) & " Thousand ", "") & IF(INT(MOD(A1,1000)/100) > 0, LOOKUP(INT(MOD(A1,1000)/100), {1,2,3,4,5,6,7,8,9}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Hundred ", "") & IF(MOD(A1,100) < 20, LOOKUP(MOD(A1,100), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}), LOOKUP(INT(MOD(A1,100)/10), {2,3,4,5,6,7,8,9}, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}) & IF(MOD(A1,10) > 0, " " & LOOKUP(MOD(A1,10), {1,2,3,4,5,6,7,8,9}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}), "") ) ) )
How the Formula Works:
- Check for Zero: If the value in cell A1 is 0, the formula returns “Zero”.
- Billions: Extracts the billions place and appends “Billion” if applicable.
- Millions: Extracts the millions place and appends “Million” if applicable.
- Thousands: Handles the thousands segment, including hundreds within thousands.
- Distinguishes between numbers below 20 (unique names) and multiples of ten.
- Hundreds: Processes the hundreds place within the remaining number.
- Tens and Units: Converts the last two digits into words, handling both teens and standard tens.
- TRIM Function: Removes any extra spaces from the final output for a clean result.
Example:
Let’s see how the formula works with an example.
- Number: 7804033
- Expected Output: “Seven Million Eight Hundred Four Thousand Thirty Three”
- Formula Output: “Seven Million Eight Hundred Four Thousand Thirty Three”
Another example:
- Number: 99999
- Expected Output: “Ninety Nine Thousand Nine Hundred Ninety Nine”
- Formula Output: “Ninety Nine Thousand Nine Hundred Ninety Nine”
Benefits of Using This Formula
- No VBA Required: Perfect for users who prefer to avoid macros or have restrictions on using VBA.
- Comprehensive Coverage: Handles numbers up to billions, making it suitable for most financial and official documents.
- Reusable: Easily apply the formula across multiple cells to convert various numbers.
Limitations
- Complexity: The formula is lengthy and can be challenging to modify for those unfamiliar with nested functions.
- Performance: For extremely large datasets, the formula might impact Excel’s performance.
- Static Structure: It doesn’t handle decimal points or negative numbers. Additional logic is required for such cases.
Tips for Using the Formula
- Cell Reference: Replace A1 with the cell reference containing the number you want to convert.
- Copying the Formula: Due to its complexity, ensure that the formula is copied correctly without missing any parts.
- Formatting: Ensure that the number is in a numerical format without any special characters or spaces.
- Testing: Test the formula with various numbers to ensure it meets your requirements.
Excel Formula to Convert Numbers to Words Using LAMBDA Function
Update: Now You Can Use the Newly Introduced LAMBDA Function!
In addition to the VBA code we’ve previously discussed for converting numbers to words in Excel, we now introduce a powerful and efficient method using the new LAMBDA function. This method simplifies the process, eliminating the need for VBA and allowing you to achieve the same results directly within your worksheet. The LAMBDA function, a recent addition to Excel, enables you to define custom functions using Excel formulas, making your workbook more dynamic and versatile.
The LAMBDA Formula
Below is the LAMBDA function that converts numbers to words for values ranging from 0 up to 10 million. This formula is comprehensive and handles various numeric ranges including units, tens, hundreds, thousands, and millions.
=LAMBDA(num, LET( singleDigits, {"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"}, teens, {"Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"}, tens, {"", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"}, units, MOD(num, 10), tensPlace, MOD(INT(num / 10), 10), hundredsPlace, MOD(INT(num / 100), 10), thousandsPlace, MOD(INT(num / 1000), 1000), millionsPlace, INT(num / 1000000), words, IF( num < 10, INDEX(singleDigits, num + 1), IF( num < 20, INDEX(teens, num - 9), IF( num < 100, INDEX(tens, tensPlace + 1) & IF(units <> 0, "-" & INDEX(singleDigits, units + 1), ""), IF( num < 1000, INDEX(singleDigits, hundredsPlace + 1) & " Hundred" & IF(MOD(num, 100) <> 0, " " & NUM2WORDS(MOD(num, 100)), ""), IF( num < 1000000, NUM2WORDS(INT(num / 1000)) & " Thousand" & IF(MOD(num, 1000) <> 0, " " & NUM2WORDS(MOD(num, 1000)), ""), IF( num < 10000000, NUM2WORDS(millionsPlace) & " Million" & IF(MOD(num, 1000000) <> 0, " " & NUM2WORDS(MOD(num, 1000000)), ""), "Number out of range" ) ) ) ) ) ), words ) )
Steps to Define the LAMBDA Function
- Open Excel.
- Go to the “Formulas” tab.
- Click on “Name Manager”.
- Click on “New” to create a new named formula.
- In the “Name” field, enter NUM2WORDS.
- In the “Refers to” field, paste the above LAMBDA function.
Using the NUM2WORDS Function
Once you’ve defined the NUM2WORDS named formula, you can use it in any cell within your worksheet. Simply enter the formula =NUM2WORDS(cell_reference) where cell_reference is the cell containing the number you want to convert. For example:
=NUM2WORDS(A1)
This function will convert the numeric value in cell A1 to its corresponding word form, providing a seamless and automated way to enhance your Excel projects.
- If you type the formula =NUM2WORDS(35500), it will give you: Thirty-Five Thousand Five Hundred
- If Range A1=5050000, it will output: Five Million Fifty Thousand.
- You can customize to suite your needs based on your data
Note: Maximum Range
This LAMBDA function supports converting numbers up to 10 million. If you need to handle larger numbers, you will need to extend the logic further to accommodate those values.
Conclusion
Note: Keep in mind that this VBA function will only work with whole numbers between 0 and 999999999999999. If you need to work with very large numbers or decimals, the code would need to be modified.
We hope you found our post on converting numbers to words in Excel valuable and practical. Your thoughts and feedback are extremely important to us!
If you’ve tried this technique or have any questions about it, please feel free to share your experiences in the comments below. We’d love to hear how this function has worked for you or if there are any other methods you’ve found useful.
Your input helps us make our content better and more relevant to your needs. Additionally, if you’ve encountered any issues or difficulties along the way, share those as well so we can improve our instructions.
Thank you again for reading our blog. Your engagement helps us create more useful content for you and other readers.

your VBA code is working ggreat, great Job
Thank you for all you support!
will it work with currency?
Yes! You can format the data using Excel Number Formatting (Currency).
When I type that formula I got the answer as “#NAME?
Please make sure to insert a module and paste the provided VBA code.
How to implement for decimals?
I can’t able to convert in rupees
Please check this this INR formula
can give a formula of convert number to hindi word
I followed the steps for the Lambda code in Excel 365 but when I enter the formula it does not recognize it as a formula. It looks like a text.
Please make sure that you have Microsoft 365 installed in your PC.
thank you
it help
You’re welcome, we are glad it helped!
For INR (₹) user’s
Function InWord(ByVal pNumber)
Dim Dollars As String, Cents As String
Dim arr()
arr = Array(“”, “Thousand”, “Lakh”, “Crore”)
pNumber = Trim(Str(pNumber))
‘ Check for decimal part and handle paisa
Dim xDecimal As Integer
xDecimal = InStr(pNumber, “.”)
If xDecimal > 0 Then
Cents = GetTens(Left(Mid(pNumber, xDecimal + 1) & “00”, 2))
pNumber = Trim(Left(pNumber, xDecimal – 1))
End If
‘ Process integer part by splitting into groups
Dim xIndex As Integer
Dim GroupCount As Integer
GroupCount = 0
Do While Len(pNumber) > 0
Dim xHundred As String, xValue As String
If GroupCount = 0 Then
xValue = Right(pNumber, 3) ‘ First three digits from the right
pNumber = Left(pNumber, Len(pNumber) – 3)
Else
xValue = Right(pNumber, 2) ‘ Next two digits
pNumber = Left(pNumber, Len(pNumber) – 2)
End If
xHundred = “”
If Val(xValue) 0 Then
If Len(xValue) = 3 Then
xHundred = GetDigit(Left(xValue, 1)) & ” Hundred ” & GetTens(Right(xValue, 2))
Else
xHundred = GetTens(xValue)
End If
End If
If xHundred “” Then
Dollars = xHundred & ” ” & arr(GroupCount) & ” ” & Dollars
End If
GroupCount = GroupCount + 1
Loop
‘ Clean up and format the result
Dollars = Replace(Trim(Dollars), ” “, ” “)
Select Case Dollars
Case “”
Dollars = “Rupees Only”
Case “One”
Dollars = “One Rupee”
Case Else
Dollars = Dollars & ” Rupees”
End Select
Select Case Cents
Case “”
Cents = “”
Case “One”
Cents = ” and One Paisa only”
Case Else
Cents = ” and ” & Cents & ” Paisa only”
End Select
InWord = Dollars & Cents
End Function
Function GetTens(pTens)
Dim Result As String
Result = “”
If Val(Left(pTens, 1)) = 1 Then
Select Case Val(pTens)
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(pTens, 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(pTens, 1))
End If
GetTens = Result
End Function
Function GetDigit(pDigit)
Select Case Val(pDigit)
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
hi, what formular spellnumber for no 30.79 in word thirty and cents seventy-nine only
Hi Sir, how to modify the decimal?
Stay blessed. Thank you!
You are welcome!
everytime i close the excel the code also gone
If you are using the VBA Code, you must save the Excel File as Macro Enabled File (.xlsm) format.
Regards
PNRao!