There are Excel formulas and User Defined Functions (UDF) that can change numbers into words. Those are handy if you’re typing a number into a workbook, and want the written amount to be shown, as it might appear in a cheque. Have you ever tried to do the opposite – change words to numbers in Excel?
Words to Numbers
It’s a tricky process to change words to numbers in Excel, and Excel MVP, Jerry Latham, has created an Excel UDF – WordsToDigits – to help you out.
With the WordsToDigits UDF, you can create a worksheet formula to translate words into their numeric value. For example:
- Five hundred fifty-two becomes 552
- One dollar and 27 cents becomes 1.27
In the first example, the words are in cell B2, and the formula in cell C2 is:
=WordsToDigits(B2)
You can format the formula results in any number format you want, such as general numbers, numbers with thousands separators, or currency.
Refer to Worksheet Cells
Although the WordsToDigits UDF is quite flexible, it only works with cell references, not text strings. For example, this formula in cell C2:
=WordsToDigits(“twelve”)
will result in a #VALUE! Error.
However, if you type “twelve” in cell B3, and refer to that cell in the formula, the result is correct. Here is the formula that’s entered in cell C3:
=WordsToDigits(B3)
WordsToDigits Tips
Jerry passed along these tips, for working with the WordsToDigits UDF:
- It can handle numbers up to 999,999,999,999,999.
- The accuracy of any decimal portion decreases as the number of digits increases. But at normally used value ranges such as up through millions, this should not be a significant issue.
- Any fractional parts of a number must be entered as digits, as:
- six and 7/100
- twelve hundred dollars and 27 cents
- Misspelling of numeric words will result in them being interpreted as zero, so typos can give inaccurate results.
Note: The WordsToDigits UDF is based on the short scale system of naming large numbers.
Experiment With the WordsToDigits UDF
You can download Jerry’s workbook, to see the UDF code, and his examples of how to use the function.
Go to Jerry’s Sample Files page on my website. Then, in the VBA section, look for JL0001 — Words to Numbers in Excel.
To test the UDF, you can create your own entries on the workbook’s Sample Entries sheet.
If you have questions or comments, please share them with Jerry, in the comments below.
___________________
Words to Numbers in Excel
[…] convert numbers to words. (And if you want to do the other way round, ie, words to numbers, you can check this out […]
Nice stuff….thank you…
hi,
i am not getting the result after using this formula in excel 2003.An error is coming #name
Where do I get the UDF to download for the addins ?
I find it very useful for Excel Users
Thank you.
Regards
Hello sir
i am same above format triad in excel but error coming.
what can i do
Which is Very useful day 2 day life this number to words
if possible this tamil ? number to words
Hey,
Looks very useful, I just followed the guidance but i got the same error “#Name?”
@Raza, @SHAIK, @Jay — Did you see a Security Warning message when you opened the file, asking you to enable macros? If not, your security setting might be High, and the macros aren’t working, so the formula result is an error.
What a great sharing in a simple manner.Very nice
http://xcell05.free.fr/morefunc/english/nbtexte.htm
another option which worked…
Hi
can i get any other command in excel which is opposite like digitstowords?
Thanks in advance
ALL: If you are getting a #Name! error, make certain that you have “Enabled” macros. When you open the workbook. In Excel 2007/2010 the alert that macros have been disabled and must be enabled will appear in a yellow bar near the top of the worksheet.
You may have to change the settings of your Macro Security – a little more complex process, but you could ask for detailed instructions on how to do that at this website if you have a Windows passport account:
http://answers.microsoft.com/en-us/office/forum/excel?tab=all
vinayak: a good routine for taking numbers and turning them into words is right here at Contextures. Ron Coderre shared his macro for this here:
http://www.contextures.com/excelfilesRon.html#RF0001
For those of you that have found the routine useful or simply intriguing, THANK YOU.
hello,
i have tried everything but the formula for words to numbers (wordstodigits(d2)) is not working please help me i have to submit my assignment….. plz…..
All,
First, to zarian, sorry I was not aware that you had made the request. I do not receive alerts that posts have been made here.
In the future if someone needs help with the file that does not require a complete rewrite, then you may contact me at the following email address (Put it all back together to make a proper email address)
HelpFrom
@
JLathamSite
.
com
Please attach a copy of the workbook to the email and explain what issue(s) you are having.
hello,
i have tried the formula for words to numbers wordstodigits(d2) is not working please help me i have to submit my assignment….. plz…..