Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
How to CONVERT Numbers to Words with Built-in Excel Formula?

- Home
- :
- Excel
- :
- General Discussion
- :
- How to CONVERT Numbers to Words with Built-in Excel Formula?

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 07 2018 07:10 AM

I am writing cheque and invoices that must have the dollar amounts typed in words and it is hectic to type the numbers and then type words manually. For example $541,125.57 should convert to five hundred forty-one thousand one hundred twenty-five Dollars and fifty-seven Cents

I cannot use Macros/VBA, the only solution to work for me is built-in Excel formula or anything that would not use Macros.

Thanks.

Labels:

17 Replies

Best Response confirmed by
Flora Sim* (Occasional Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 07 2018 07:21 AM - edited Mar 07 2018 07:24 AM

SolutionI had seen this question quite often, from colleagues, Q&A forums etc, but while there were many UDF solutions, there wasn't any solution with Built-In Formula (at least not to my knowledge).

You lucked out, as I had already written this Formula couple of years ago.

Here is the formula. I have also uploaded the file with formula.

It only has one limitation, It cannot convert more than number $999,999,999,999.00 "Nine Hundred Ninety-Nine Billion Nine Hundred Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred Ninety-Nine Dollars and Zero Cent"

But I think it will do the job for you.

=IF(A1<=0,"Referred Cell Must be Empty",IF(A1>10^12-0.01,"Exceeds Maximum which is 999999999999",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0, 12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety "),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A1,1)>1," dollars"," ")&IF(AND(A1>=1,A1<2),"dollar",""))&" and "&IF(ISERROR(FIND(".",A1)),"Zero Cent",PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" cents","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" cents","twent y","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine")&" cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="00","zero cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="01","one cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" cents",""))))))))))

- Tags:
- Excel formulas

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 07 2018 07:40 AM - edited Mar 07 2018 07:41 AM

Hi Flora,

In fact, there is no built-in function to do that.

I do not think this can easily be achieved using a formula.

But always there is a solution with VBA/Macros.

Please let us know why you can not use the VBA/Macros?

Is it disabled on your computer?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 07 2018 07:47 AM

Wow!

THANK YOU SO VERY MUCH!

THANK YOU SO VERY MUCH!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 07 2018 07:51 AM

Haytham,

Due to corporate domain environment administration setting, macros are blocked by default in our office domain.

I got an excellent solution with built-in formulas provided by Jamil Mohammad.

Thanks

Due to corporate domain environment administration setting, macros are blocked by default in our office domain.

I got an excellent solution with built-in formulas provided by Jamil Mohammad.

Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 27 2019 02:04 AM

Brilliant! Just replace Dollar to your own currency and you are good to go. This is amazing work.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 07:44 AM

Hi dear sir ,

I am very happy to see your formula about Numbers to Words converter. from long time i was searching it. I use your formula and its working fine. But there is a little problem. I am living in Bahrain. I already choose Bahraini Dinar to small currency fils. But fils if I write in cell, for example 450.350 BD . It should be four hundred and fifty BD and three hundred fifty fils. But your formula said four hundred and fifty BD and thirty five fils. But 3rd decimal Place your formula is not calculating. Can you help me please ? Waiting for your reply. Thanks If you can contact me on my whats-app it will be also great . +97338103220. Waiting for your reply. Thanks

Best Regards

Azeem

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 07:47 AM

@Flora Sim Not what you're looking for, but a good piece of Excel trivia: This functionality is built-in, but only for Thai Baht: =BAHTTEXT.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 07:49 AM

Hi dear sir, i already changed Dollar to Bahraini Dinar , but this is work for 2 decimal numbers , i want 3 decimal numbers . Because Bahrain Currency have one thousand fills then its equal to 1 Bahrain Dinar. If i write 0.530 BD converted to words cell fifty three fills . It should be five hundred thirty fils. I hope you understand dear sir . Please help me . Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 07:55 AM

Can you describe what is the decimal separator in your Excel?

I mean for dollar it is . is it comma , for your computer?

I mean for dollar it is . is it comma , for your computer?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 08:01 AM

You need to change your computer regional setting. because from what you describe, it appears that your computer regional setting is recognizing dot as thousand separator and comma as decimal points.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 08:02 AM

@Jamil Mohammad Hi sir , I just copy past your formula , and just go to Formula and change all dollars in BD and Cents in to Fils . I not change any comma . Can you check and help me or just check your formula and put BD and fills. But fills should be in 3 digits . Like we have one thousand fils in one Bahraini Dinar. Have you understand sir ? Please reply. thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 08:44 AM

Ok. I will reply later today.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 10:46 AM

I have written another formula to suit your needs. also in the attached workbook.

=IF(A1<=0,"Referred Cell Must be Empty",IF(A1>10^12-0.01,"Exceeds Maximum which is 999999999999",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu

ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,

12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety

"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A1,1)>1," BDs"," ")&IF(AND(A1>=1,A1<2),"BD",""))&" and "&CONCATENATE(CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),".",REPT(" ",255)),255,200))),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety

"),IF(VALUE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))&IF(FLOOR(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)>1," fils"," ")&IF(AND(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))>=1,TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))<2),"Fil",""))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 10 2020 11:05 PM

@Jamil Mohammad Hi dear sir , thanks so much for your great help. First time i am using a comunity and its help me a lot. I am really happy dear sir . God bless you so much . Good luck in your whole life sir , Amen. thanks again. Best regards Azeem

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 11 2020 12:01 AM

Hi dear sir ,

So sorry to disturb you again, This formula you make for me , is very good and working perfect . But there is a little issue, if i write , 35.251 BD then its result is thirty five BD and two hundred fifty one fils. But if i write 35.250 BD then the result is thirty five BD and twenty five fils . This result should be thirty five BD and two hundred fifty fils . Can you check and help me again please . You are helping me a lot , thanks so so so much dear sir .

Best Regards

Azeem

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 12 2020 12:58 AM

@Azeem143 Hi dear sir , there is another issue also in the formula. If I write simple 450 BD , there is #VALUE! ... If i write 450.225 then result is Four Hundred Fifty Bds and Two Hundred Twenty-Five fils. Do you understand dear sir . Please reply. Waiting for your reply. Thanks

Regards

Azeem

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 13 2020 05:49 PM

Hi, I modified the formula, because the formula length became too long, so I had to use a helper cell in column c for splitting the formula into half and then use C1 into the B1 formula as a reference.

please see attached.