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

@Twifoo 

Outstanding work, really amazing and very much useful specially for finance people.

Tauqeer

@tauqeeracma 

Thanks for your compliment. Incidentally, you need not copy the workbook. You only have to copy the formula to any cell other than those in Column A and it will convert the number to its left to words. 

Thereafter, you can convert any number in the same workbook by entering this formula in the cell to its right: 

=NumToWords

@Twifoo  

Hi @Twifoo Twifoo, not sure if i am entering the correct formula. I pasted the NumtoWords formula from your sheet then put a number eg. 345 but it did nothing. I would appreciate if you guide me how to use it. 

 

Thank you in advance! 

@metalarmy 

For example, enter 1234.56 in A1 of your worksheet. Copy the cell containing the NumToWords formula and enter it in B1

You must see the words in B1 after that. 

thats correct..this is the formula NumToWords=IF(CellLeft<0,"Negative ","")&IntTxt&DecTxt
that I am entering in B1 and entering number 1234.56 in A1 and it shows nothing. Do i have to add anything before or after the formula or just the plain formula ? I am using Excel version 1908 from from microsoft office 365 pro plus

@metalarmy 

Perhaps, the attached sample file will help you understand how the formula works. Please look at the formula in B2, as shown in the snapshot below: 

NumToWords Sample.PNG

@Twifoo Yes yes yes ..thank you so much, now the file that you sent works like a charm... what did you do.??? .just trying to understand and what i wasnt doing.....

@metalarmy 

I simply copied the formula in B2 of my original file to B2 of the new workbook with 1234.56 in A2. Perhaps, you copied the wrong cell. 

@Twifoo ohh ok...I understood now...for so long i was copying the formula from B18 and pasting in a new workbook and then entering a number before it.... :( thank you so much dear...you help is really appreciated!! 

Good job
You’re very much welcome!
why numer to words dont work on sheets made by me but works perfectly fine on your sheets sir
As I stated earlier, you may test the formula by entering 1234.56 in A2 of your worksheet. Then, copy cell B2 in my original file and enter it in B2 of your worksheet.

@Twifoo 

 

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.

@mathetes 

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

its working fine, but i want to know that why this formula works in your sheet by default, but not in mine? is there some reasona that i am not aware of? please tell

regard
Saty_Rana

@Saty_Rana 

My NumToWords is a named formula, not a built-in function. You must copy B2 of my worksheet to B2 of yours to store the named formula into your Name Manager. Without copying B2, NumToWords won't work in your worksheet because it is not stored in your Name Manager. 

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: 

NumToDollars Sample.PNG

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

Thank you sir
1 best response

Accepted Solutions
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.

View solution in original post