Words to Numbers in Excel

image 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 into numbers? It’s a tricky process, and Excel MVP, Jerry Latham, has created an Excel UDF – WordsToDigits – to help you out.

Words to Numbers

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

wordtodigits01

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.

wordtodigits03

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.

wordtodigits02

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.

___________________

You may also like...

17 Responses

  1. […] convert numbers to words. (And if you want to do the other way round, ie, words to numbers, you can check this out […]

  2. Nasreen Rassulmia says:

    Nice stuff….thank you…

  3. raza says:

    hi,
    i am not getting the result after using this formula in excel 2003.An error is coming #name

  4. Goh Hock Siew says:

    Where do I get the UDF to download for the addins ?

    I find it very useful for Excel Users

    Thank you.

    Regards

  5. SHAIK says:

    Hello sir
    i am same above format triad in excel but error coming.

    what can i do

  6. Mothilal says:

    Which is Very useful day 2 day life this number to words

  7. Mothilal says:

    if possible this tamil ? number to words

  8. Jay says:

    Hey,

    Looks very useful, I just followed the guidance but i got the same error “#Name?”

  9. @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.

  10. Wilson says:

    What a great sharing in a simple manner.Very nice

  11. vinayak says:

    Hi
    can i get any other command in excel which is opposite like digitstowords?
    Thanks in advance

  12. Jerry Latham says:

    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.

  13. zarian says:

    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…..

  14. Jerry Latham says:

    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.

  15. mahfuz says:

    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…..

  1. May 18, 2013

    […] buena y una mala: 1. Este codigo lo hace: Words to Numbers in Excel | Contextures Blog 2. Esta en ingles, tendras que traducir todos los numeros en el codigo a […]

Leave a Reply

Your email address will not be published. Required fields are marked *