Forum Discussion
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.
- Great work Twifoo
well !! if we edit "=IF(CellLeft<0,"Negative ","")&IntTxt&DecTxt " and re write as "=IF(CellLeft<0,"Negative ","")&IntTxt" then output show only figure in words and "& 0/100" not shown
- Hi Twifoo Sir,
Could you please help me on this regarding numbers to words without a VBA code
I need a formula for numbers to words without a VBA code. My currency is Taka instead of Dollars and after the decimal, it's called Paisa instead of Cent. Also require the word Only, in the last.
Here is the sample for a better understanding
1 = One Taka Only
10 = Ten Taka Only
10.50 = Ten Taka Fifty Paisa Only
100 = One Hundred Taka Only
1000 = One Thousand Taka Only
10000 = Ten Thousand Taka Only
100000 = One Lac Taka Only (We called Lac for one hundred thousand)
1000000 = Ten Lac Taka Only
10000000 = One Crore Taka Only
Please help me with this formula.
Thanks in advance.Hi istiakahmed
NumToWords template designed by the respected expert member Mr. Twifoo is based on an international number system (Unit/Ten/Hundred/Thousand etc.), whereas you need the conversion of numbers into words based on your local (Bangla/Taka) numbering system.
I have modified the attached file and hope it will address your requirements.
Please go through it and let me know if it works for you.
Thanks
Tauqeer
- Hi tauqeeracma,
Thanks to you & Mr. specially to Mr. Twifoo for his continuous support for this kind of special formula. It's working fine in same sheet with "=@Conversion" in any cell next to the amount cell. But how it will work in a specific cell "
Regards,
Istiak Ahmed
Awesome! Is it possible to remove the fraction function? For example, I want "2" to be converted to "Two", without having it be "Two & 0/100". Thanks! Twifoo
thanks for your quick reply!
No, it is not about translation. I can handle that in your formula.Using the names manager in Excel I can translate the words in SfxList, TxtList1 and TxtList2.
My problem lies in the syntax of German numbers:
Where English has "Twenty-four", "Thirty-one", German language switches the digits to "Vierundzwanzig", "Einunddreissig". ("Fourandtwenty", "Oneandthirty").
As far as I understand your algorithm, I would have to swap the order of OneTxt and TenTxt in IntTxt!?
"Twenty-" in TxtList2 would translate to "undzwanzig" ("andtwenty") etc.
Best regards and have a good weekend! ;)>
I'll get help. I hope you help me (although I know I'm at the computer level). I'm not ashamed of my ignorance. Because really: "The world is full of opposites.".
I only do Excel calculations for myself. I have a problem with one calculation. Specifically: the same numbers, but the same function, but the result is different in both equivalent calculations. If only I knew what the correct function must be to get the right result (and it's the same number in both identical operations). I will send you a file (Microsoft Office Home and Student 2013). Will you send me back the correct formula.
I watched the "Excel NumToWords Formula" level. I saved your NumWords.xlsx file. The function works! How, I do not understand… but I say only to myself: I am learning!
- I recommend that you post your problem as a new conversation so that you could obtain more solutions.
In the attached file I need to enter the month in the first tab where I have highlighted which should pick the month in second tab and post the total from the third tab to the second tab in the appropriate month.
Can you help me please?
Kind Regards
Soundra Lawrence
You need to post this as the start of a new thread. It is not a continuation of the existing conversation....
However, now that I've taken a few minutes to look at that workbook, I would suggest you go back to whoever it was that designed it and ask for clarification (and fixes) there. It's clearly meant as a template for personal cash flow management, but is not fully functional at this point (unless you inadvertently made changes to it that adversely affected it).
- Perfect suggestion!
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.
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
- Good job
