by In this article I will show you two quick and free ways to convert currency numbers into English words in Excel 2019, 2016, 2013 and other versions.
Microsoft Excel is a great program to calculate this and that. It was initially developed to process large data arrays. However, it also lets creating accounting records like invoices, evaluation or balance sheets quickly and effectively.
In more or less solid payment documents it is necessary to duplicate numeric values with their word form. It is much harder to falsify typed numbers than those written by hand. Some swindler can try to make 8000 out of 3000, while it is almost impossible to secretly replace "three" with "eight".
So what you need is not just convert numbers to words in Excel (e.g. 123.45 to "one hundred and twenty three, forty five"), but spell out dollars and cents (e.g. $29.95 as "twenty nine dollars and ninety nine cents" ), pounds and pence for GBP, euros and eurocents for EUR, etc.
Even the latest versions of Excel don't have a built-in tool for spelling numbers, not to mention earlier versions. But that is when Excel is really good. You can always improve its functionality using formulas in all their
combinations, VBA macros, or third-party add-ins.
Below you'll find two ways to convert numbers from figures to words
And, possibly, you may need to convert Words to Numbers in Excel
Note. If you are looking for the number to text conversion, which means you want Excel to see your number as text, it's a bit different thing. For this, you can use the TEXT function or a few other ways described in How to change numbers to text in Excel.
SpellNumber VBA macro to convert numbers to words
As I have already mentioned, Microsoft didn't want to add a tool for this task. However, when they saw how many users needed it, they created and published the special VBA macro on their website. The macro does what its name SpellNumber suggests. All other macros I came across are based on the Microsoft code.
You can find the macro mentioned as "spellnumber formula". However, it is not a formula, but a macro function, or to be more precise Excel User defined function (UDF).
The spellnumber option is able to write dollars and cents. If you need a different currency, you can change "dollar" and "cent" with the name of your one.
If you are not a VBA savvy guy, below you will find a copy of the code. If you still don't want or haven't time to sort this out, please use this solution.
- Open the workbook where you need to spell the numbers.
- Press Alt+F11 to open the Visual Basic editor window.
- If you have several books opened, check that the needed workbook is active using the list of projects in the upper left corner of the editor (one of the workbook elements is highlighted with blue).
- In the editor menu go to Insert-> Module.
- You should see a window named YourBook - Module1. Select all of the code in the frame below and paste it to this window.
Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " MyNumber = Trim(Str(MyNumber)) DecimalPlace = InStr(MyNumber, ".") If DecimalPlace > 0 Then Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumber = Dollars & Cents 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 - Press Ctrl+S to save the updated workbook.
You will need to resave your workbook. When you try to save the workbook with a macro you'll get the message "The following features cannot be saved in macro-free workbook"
Click No. When you see a new dialog, chose the Save as option. In the field "Save as type" pick the option "Excel macro-enabled workbook".
Use SpellNumber macro in your worksheets
Now you can use the function SpellNumber in your Excel documents. Enter =SpellNumber(A2) into the cell where you need to get the number written in words. Here A2 is the address of the cell with the number or amount.
Here you can see the result:
Voila!
Quickly copy the SpellNumber function to other cells.
If you need to convert the entire table, not just 1 cell, place your mouse cursor to the lower right corner of the cell with the formula until it turns into a small black cross:
Left-click and drag it across the column to fill in the formula. Release the button to see the results:
Note. Please keep in mind that if you use SpellNumber with a link to another cell, the written sum will be updated each time the number in the source cell is changed.
You can also enter the number directly into the function, for example, =SpellNumber(29.95) (29.95 - without quotation marks and the Dollar sign).
Disadvantages of using macro to spell numbers in Excel
First off, you must know VBA to modify the code according to your needs. It is necessary to paste the code for each workbook, where you plan to change it. Otherwise, you will need to create a template file with macros and configure Excel to load this file at each start.
The main disadvantage of using a macro is if you send the workbook to somebody else, this person will not see the text unless the macro is built into the workbook. And even if it's built-in, they will get an alert that there are macros in the workbook.
Spell numbers into words using a special add-in
For Excel users who need to quickly spell sums but don't have time to learn VBA or figure out workarounds, we created a special tool that can quickly perform the amount-to-words conversion for a few popular currencies. Please meet the Spell Number add-in included with the latest release of our Ultimate Suite for Excel.
Besides being ready for use, the tool is really flexible in converting amounts to text:
- You can select one of the following currencies: USD, EUR, GBP, BIT, AUD.
- Spell the fractional part in cents, pennies, or bitcents.
- Choose any text case for the result: lower case, UPPER CASE, Title Case, or Sentence case.
- Spell the decimal part in different ways.
- Include or omit zero cents.
The add-in supports all modern versions including Excel 365, Excel 2029, Excel 2016, Excel 2013, and Excel 2010. Please feel free to explore other capabilities on the product's home page linked above.
And now, let's see this number spelling utility in action:
- Select an empty cell for the result.
- On the Ablebits tab, in the Utilities group, click Spell Number.
- In the Spill Number dialog window that appears, configure the following things:
- For the Select your number box, pick the cell containing the amount you want to get written as text.
- Specify the desired currently, letter case and the way the decimal part of the number should be spelled.
- Define whether to include zero cents or not.
- Choose whether to insert the result as a value or formula.
- At the bottom of the dialog window, preview the result. If you are happy with the way your number is written, click Spell. Otherwise, try different settings.
The screenshot below demonstrates the default choices and the spelled number in B2. Please notice a formula (more precisely, a user-defined function) in the formula bar:
And this is a quick demonstration of how other currencies can be spelled out:
Tips and notes:
- Because the Spell Number add-in was designed to handle real-life use cases such as invoices and other financial documents, it can only convert one number at a time.
- To spell a column of numbers, insert a formula in the first cell, and then copy the formula down.
- If there is chance that your source data may change in the future, it's best to insert the result as formula, so it updates automatically as the original number changes.
- When choosing the result as formula option, a custom user-defined function (UDF) is inserted. If you plan to share your workbook with someone who does not have the Ultimate Suite installed, remember to replace formulas with values before sharing.
Reverse conversion - English words into numbers
Frankly, I can't imagine why you may need it. Just in case… :)
It appears that Excel MVP, Jerry Latham, created such Excel User defined function (UDF) as WordsToDigits. It converts English words back to number.
You can download Jerry's WordsToDigits workbook to see the UDF code. Here you'll also find his examples of how to use the function.
You can see how the function works on the sheet "Sample Entries", where you will also be able to enter your own examples. If you plan to employ WordsToDigits in your documents, please be informed that this function has restrictions. For example, it doesn't recognize fractions entered in words. You will find all the details on the "Information" sheet.
528 comments
I Like This
after closing excel file and open again this method doesn't work.
please help me!
since our currency is peso and the cents are express as centavos, i modified the code to reflect our currency...
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Pesos, Centavos, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Centavos = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Pesos = Temp & Place(Count) & Pesos
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Pesos
Case ""
Pesos = "No Pesos"
Case "One"
Pesos = "One Peso"
Case Else
Pesos = Pesos & " Pesos"
End Select
Select Case Centavos
Case ""
Centavos = " "
Case "One"
Centavos = " and One Centavo"
Case Else
Centavos = " and " & Centavos & " Centavos"
End Select
SpellNumber = Pesos & Centavos
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
hello sir please provide code for Indian currency
example. 100000 = one lac only
1000 = one thousand
100 = one hundred
1000000 = ten lac only
10000000 = one crore only
hello sir please provide code for Indian currency
example. 100000 = one lac only
1000 = one thousand
100 = one hundred
1000000 = ten lac only
10000000 = one crore only
hi,
I need to know how i can break the lines of number in words as if the word is too long i need to print in the second line of cheque. how i can do that.
Thank you !
Well done for Dinars and Euros !
i got a problem with the excel which is unknown format number, i want to convert that in to 10 digit mobile numbers
EX - ffffec0704fb682e5547b7d0d3da5527
Can someone please help me making such a code that I only get "numbers converted into words",in excel i.e., without dollar or any any currency attached to it. Say I want to convert 2, 34 and 56, can it directly read two, thirty four , and fifty six respectively.
I will appreciate such help.
Hi Dear ,
I tried converting into indian rupees .
But CTRL+H function is not working
It is showing the error when I type "Dollars"as ' we couldnt find what you are looking for "
Pls help
Hi,
I'm trying to convert nums to words (in indian currency: like 448787 to four lakh fourty eight thousand seven hundred eighty seven only),but its not happening.
Please help me & take me out of the problem.
Hi,
I'm trying to convert nums to words (in indian currency: like 448787 to four lakh fourty eight seven hundred eighty seven only),but its not happening.
Please help me & take me out of the problem.
I want to modify it for Indian system, I changed Dollar to Rupee, but changing Millions to Lakhs wont be correct as Million has 6 zeroes and Lakhs as 5. the array defined has terms differing by 3 zeroes, can you help out with modifying the array to incorporate Lakhs
How can numbers be converted with no currency and if no cents to ignore cents bit
Example
5,455/= Five thousand Four hundred fifty five only
5,455.50/= Five thousand Four hundred fifty five and Fifty cents only
How to convert as follows:-
1. 1,234 = One Thousand Two hundred And Thirty Four
2. 1,200 = One Thousand And Two hundred?
3. 1,202.50 = One Thousand Two hundred Two And Cents Fifty?
4. 11,010 = Eleven Thousand And Ten?
Function NumToWord(ByVal N As Currency) As String
Const Ten = 10@
Const Hundred = Ten * Ten
Const Thousand = Ten * Hundred
Const Lakh = Thousand * Hundred
Const Crore = Lakh * Hundred
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion
If (N = 0@) Then NumToWord = "zero": Exit Function
Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1
If (N >= Crore) Then
Buf = Buf & NumToWordDigitGroup(Int(N / Crore)) & " Crore"
N = N - Int(N / Crore) * Crore
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= Lakh) Then
Buf = Buf & NumToWordDigitGroup(Int(N / Lakh)) & " Lakh"
N = N - Int(N / Lakh) * Lakh
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= Thousand) Then
Buf = Buf & NumToWordDigitGroup(N \ Thousand) & " Thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= Hundred) Then
Buf = Buf & NumToWordDigitGroup(N \ Hundred) & " hundred"
N = N Mod Hundred
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= 1@) Then
Buf = Buf & NumToWordDigitGroup(N)
End If
NumToWord = Buf
End Function
Private Function NumToWordDigitGroup(ByVal N As Integer) As String
Const Hundred = " hundred"
Const One = "one"
Const Two = "two"
Const Three = "three"
Const Four = "four"
Const Five = "five"
Const Six = "six"
Const Seven = "seven"
Const Eight = "eight"
Const Nine = "nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False
Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select
If (Flag False) Then N = N Mod 100
If (N > 0) Then
If (Flag False) Then Buf = Buf & " "
Else
NumToWordDigitGroup = Buf
Exit Function
End If
Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "twenty": Flag = True
Case 3: Buf = Buf & "thirty": Flag = True
Case 4: Buf = Buf & "forty": Flag = True
Case 5: Buf = Buf & "fifty": Flag = True
Case 6: Buf = Buf & "sixty": Flag = True
Case 7: Buf = Buf & "seventy": Flag = True
Case 8: Buf = Buf & "eighty": Flag = True
Case 9: Buf = Buf & "ninety": Flag = True
End Select
If (Flag False) Then N = N Mod 10
If (N > 0) Then
If (Flag False) Then Buf = Buf & "-"
Else
NumToWordDigitGroup = Buf
Exit Function
End If
Select Case (N)
Case 0:
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "ten"
Case 11: Buf = Buf & "eleven"
Case 12: Buf = Buf & "twelve"
Case 13: Buf = Buf & "thirteen"
Case 14: Buf = Buf & "fourteen"
Case 15: Buf = Buf & "fifteen"
Case 16: Buf = Buf & "sixteen"
Case 17: Buf = Buf & "seventeen"
Case 18: Buf = Buf & "eighteen"
Case 19: Buf = Buf & "nineteen"
End Select
NumToWordDigitGroup = Buf
End Function
Hello
I want convert to Quantity PCS example : one hundred and ten pcs only.
but i don't know how to write down code
How to convert the 1,234 into One Thousand two hundred and thirty four
Thank you everyone who made and updated this code. I appreciate it very much.
There was a small glitch if the ones digit was a zero, where $820.00 would read "Eight hundred Twenty and No Cents" (two spaces between "Twenty" and "and"). I remedied this by removing the space in front of Thousand, Million, Billion, Trillion, as well as the statements for cents, and Hundred. Then I added a space to the end of Ten through Nineteen, and Twenty through Ninety in GetTens, and One through Nine in GetDigit.
I am trying to come up with a means to hyphenate a two-digit number like 32 (Thirty-Two) while also leaving off the hyphen from two-digit number ending in 0 like 50 (Fifty). Any suggestions are welcome.
SIR HOW CUT AND PAST THIS FUNCTION IN EXCEL SPREAD SHEET
Here are the cordings for Rupee Values
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to Rupee amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
Rupees = Rupees & " Rupees"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Rupees & Cents
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