SOLVED

Excel NumToWords Formula

Silver Contributor

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.

42 Replies
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!

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

@Soundra04 

 

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!

@Twifoo 

 

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.

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

best response confirmed by cuong (Microsoft)
Solution

@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: 

NumToPounds.PNG

The Excel file containing the NumToPounds formula is hereto attached for the benefit of those who may be interested in testing its usage.

 @Twifoo That was unbelievably quickly - I can't thank you enough, it's a huge help.

 

I will have a look at your  document and see if I can get my head round it, I must confess to being a somewhat unwilling user of Excel, CAD programs are more my thing, but I am always keen to make the tools I use better.

 

Thanks again!  

You're very much welcome!

@Twifoo 

Great formula!

BUT ;)>

in German figures it is "Vierundzwanzig" insted of "Twenty-four". The digits of a number in the range 21...99 are switched. Can you help me on with an update for your formula to German numbers? The words are no problem, just the algorithm.

@foxfossil 

I'm sorry. I don't know how to translate the words from English to German. I understand the currency in Germany now is Euros. Don't you want the currency in words to be translated to Euros instead?

@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! ;)>

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 

@Twifoo Hello, I just happened to see this now. May I know what will I do if number i will convert happen to be in J2 and I want to convert in words in B3? 

 

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.

 

tauqeeracma_0-1673560496488.png

 

Thanks

Tauqeer

100,000 = One Lac Taka Only (We called Lac for one hundred thousand)

?
not 1,000,000?
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
Hi @peiyezhu,
100,000 = One Lac Taka Only (We called Lac for one hundred thousand)
1,000,000 = Ten Lacs.