SOLVED
Home

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

Highlighted
Flora Sim
Occasional Contributor

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

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.

4 Replies
Solution

Re: How to CONVERT Numbers to Words with Built-in Excel Formula?

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

A.png

 

=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",""))))))))))

 

Re: How to CONVERT Numbers to Words with Built-in Excel Formula?

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?

Re: How to CONVERT Numbers to Words with Built-in Excel Formula?

Wow!

THANK YOU SO VERY MUCH!

Re: How to CONVERT Numbers to Words with Built-in Excel Formula?

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
Related Conversations
How can I delete Teams free organization?
Kazuaki Tauchi in Microsoft Teams on
5 Replies
Auto-Add new employees
Mathias Koprek in Microsoft Teams on
9 Replies
Linking OneDrive to New Microsoft Teams Channel
Anonymous in Microsoft Teams on
7 Replies
Adding an Existing OneNote notebook to Teams
CC Adeyemo in Microsoft Teams on
21 Replies