Forum Discussion
Solved
Excel NumToWords Formula
To revel my 20th week as a member of this EXCELlent Community, I hereby share my NumToWords formula. Delve into it in the attached file and please apprise me of your thoughts thereon.
Jay8642, your request is my command! Note that I simply edited the formulas for these defined names:
1. CurName
=IF(INT(IntNum)=0,"","Pound"&LEFT("s",INT(IntNum)>1))2. DecName
="Pen"&IF(SIGN(DecNum-1),"ce","ny")Finally, I renamed NumToDollars as NumToPounds. To satisfy your curiosity on the process of constructing the formula, you can read my articles on NumToWords and NumToDollars. The snapshot below illustrates the use of the NumToPounds formula:
The Excel file containing the NumToPounds formula is hereto attached for the benefit of those who may be interested in testing its usage.
That's great. And congratulations on your twentieth week! I think I'm in my fourth or fifth, and loving it too.
Your formula here takes me back to my earliest days with a PC...probably the late 70s.. I wrote a program (Basic) to print checks to pay bills; got a supply of checks that would go through a pin-feed, dot matrix Okidata printer.
And I wrote a routine that would translate 123.45 into "One Hundred Twenty-Three Dollars and Forty-five Cents" One feature of which I was very proud was that if the amount was, say, 1.14, the words would be "One Dollar" --- in the singular-- and same for "One Cent" if it came to that. None of that settling for "Dollars" just because that 99.9% of the time would work accurately. I notice you left off the currency label, which makes it more universal, so no problem.
But fun, right?
Thanks for sharing it.
- Concerning the singular and plural nouns, which is a matter of whether or not another “s” should be added thereto, I humbly hope that I could also be somehow proud as you with such an achievement!
Hi mathetes,
Your story inspired me to modify my NumToWords formula to consider Dollars as a currency built into it. The integer portion is described as either "Dollar" for singular or "Dollars" for plural. Conversely, the decimal portion is described as either "Cent" for singular and "Cents" for plural.
To test its accuracy, I entered my NumToDollars formula in Column B, as shown here:
As with my NumToWords, my NumToDollars can also convert any value up to 15 significant digits. Moreover, NumToDollars can be easily converted to apply to another Currency by modifying its components.
Cheers,
Twifoo
Twifoo This is absolutely great work!
Cheeky request - Is there any chance at all I could implore you to make a Pounds and Pence version of this please? I tried to edit it myself and cannot make head nor tail of how to do it - it would be absolutely invaluable in the construction sector in the UK in which I work.
If you can find the time to - thanks in advance. Or if you could point me toward what I should be doing I can have another shot myself.
Jay8642, your request is my command! Note that I simply edited the formulas for these defined names:
1. CurName
=IF(INT(IntNum)=0,"","Pound"&LEFT("s",INT(IntNum)>1))2. DecName
="Pen"&IF(SIGN(DecNum-1),"ce","ny")Finally, I renamed NumToDollars as NumToPounds. To satisfy your curiosity on the process of constructing the formula, you can read my articles on NumToWords and NumToDollars. The snapshot below illustrates the use of the NumToPounds formula:
The Excel file containing the NumToPounds formula is hereto attached for the benefit of those who may be interested in testing its usage.
Thanks for your compliment. My formula is universal, such that, as its name connotes, it converts a number to words, not to a specific currency. You're right, it was fun constructing the formula.
By the way, the formula is broken down into components to enable modification of each to convert a number to a specific currency.
Cheers,
Twifoo
