Formula to convert $$$ numbers to text.

[JohnZ1156]

I have this formula to convert numbers to text, however, if I enter 12.56, it shows cents as "56/100 Dollars". I would like it to work just as it does except show "Twelve Dollars and Fifty Six Cents". Of course, if the amount was 12.01, it would have to check for one cent, and in that case show, "Twelve Dollars and One Cent".

Here is the formula that I have: (replace $C$3 in the formula with the location of your value)

=IF($C$3=0,"",CHOOSE(LEFT(TEXT($C$3,"000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--LEFT(TEXT($C$3,"000000000.00"))=0,,IF(AND(--MID(TEXT($C$3,"000000000.00"),2,1)=0,--MID(TEXT($C$3,"000000000.00"),3,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT($C$3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT($C$3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT($C$3,"000000000.00"),3,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),
CHOOSE(MID(TEXT($C$3,"000000000.00"),3,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--LEFT(TEXT($C$3,"000000000.00"))+MID(TEXT($C$3,"000000000.00"),2,1)+MID(TEXT($C$3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT($C$3,"000000000.00"),4,1)+MID(TEXT($C$3,"000000000.00"),5,1)+MID(TEXT($C$3,"000000000.00"),6,1)+MID(TEXT($C$3,"000000000.00"),7,1))=0,(--MID(TEXT($C$3,"000000000.00"),8,1)+RIGHT(TEXT($C$3,"000000000.00")))>0),"Million ","Million "))
&CHOOSE(MID(TEXT($C$3,"000000000.00"),4,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT($C$3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT($C$3,"000000000.00"),5,1)=0,--MID(TEXT($C$3,"000000000.00"),6,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT($C$3,"000000000.00"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT($C$3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT($C$3,"000000000.00"),6,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT($C$3,"000000000.00"),6,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--MID(TEXT($C$3,"000000000.00"),4,1)+MID(TEXT($C$3,"000000000.00"),5,1)+MID(TEXT($C$3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT($C$3,"000000000.00"),7,1)+MID(TEXT($C$3,"000000000.00"),8,1)+MID(TEXT($C$3,"000000000.00"),9,1))=0,--MID(TEXT($C$3,"000000000.00"),7,1)<>0),"Thousand ","Thousand "))
&CHOOSE(MID(TEXT($C$3,"000000000.00"),7,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT($C$3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT($C$3,"000000000.00"),8,1)=0,--MID(TEXT($C$3,"000000000.00"),9,1)=0),"Hundred ","Hundred "))&
CHOOSE(MID(TEXT($C$3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT($C$3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT($C$3,"000000000.00"),9,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT($C$3,"000000000.00"),9,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&"and "&RIGHT(TEXT($C$3,"000000000.00"),2)&"/100")&" Dollars"

 

[kweaver]

I've used ASAP Utilities for this and it works exactly as you wanted.

 

[Dave Patton]

See the Lambda solution
www.mrexcel.com/board/threads/spellnumber.1233307/page-2#post-6067087

 

[Peter_SSs]

See the Lambda solution

Will that work in the OP's Excel 2021?

 

[JohnZ1156]

Convert US$ numbers to text
Here are 2 different formulas for converting US$ numbers to text.
The first shows cents as xx/100 Dollars.
The second shows cents as text.
Works for amounts up to $999,999,999.99

(Replace A4 with the location of your dollar amount.)
======================================================================================================
Example: One Million Two Hundred Thirty Four Thousand Five Hundred Sixty Seven and 89/100 Dollars

=IF(A4=0,"Zero and 00/100 ",CHOOSE(LEFT(TEXT(A4,"000000000.00"))+1,"","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--LEFT(TEXT(A4,"000000000.00"))=0,,IF(AND(--MID(TEXT(A4,"000000000.00"),2,1)=0,--MID(TEXT(A4,"000000000.00"),3,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),3,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),
CHOOSE(MID(TEXT(A4,"000000000.00"),3,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--LEFT(TEXT(A4,"000000000.00"))+MID(TEXT(A4,"000000000.00"),2,1)+MID(TEXT(A4,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(A4,"000000000.00"),4,1)+MID(TEXT(A4,"000000000.00"),5,1)+MID(TEXT(A4,"000000000.00"),6,1)+MID(TEXT(A4,"000000000.00"),7,1))=0,(--MID(TEXT(A4,"000000000.00"),8,1)+RIGHT(TEXT(A4,"000000000.00")))>0),"Million ","Million "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),4,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT(A4,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(A4,"000000000.00"),5,1)=0,--MID(TEXT(A4,"000000000.00"),6,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),6,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A4,"000000000.00"),6,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--MID(TEXT(A4,"000000000.00"),4,1)+MID(TEXT(A4,"000000000.00"),5,1)+MID(TEXT(A4,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(A4,"000000000.00"),7,1)+MID(TEXT(A4,"000000000.00"),8,1)+MID(TEXT(A4,"000000000.00"),9,1))=0,--MID(TEXT(A4,"000000000.00"),7,1)<>0),"Thousand ","Thousand "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),7,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT(A4,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(A4,"000000000.00"),8,1)=0,--MID(TEXT(A4,"000000000.00"),9,1)=0),"Hundred ","Hundred "))&
CHOOSE(MID(TEXT(A4,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),9,1)+1,"Zero ","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A4,"000000000.00"),9,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))

&"and "&RIGHT(TEXT(A4,"000000000.00"),2)&"/100 ")&"Dollars"

======================================================================================================

Example: One Million Two Hundred Thirty Four Thousand Five Hundred Sixty Seven Dollars and Eighty Nine Cents

=IF(A4=0,"Zero Dollars and Zero Cents",CHOOSE(LEFT(TEXT(A4,"000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--LEFT(TEXT(A4,"000000000.00"))=0,,IF(AND(--MID(TEXT(A4,"000000000.00"),2,1)=0,--MID(TEXT(A4,"000000000.00"),3,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),3,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),
CHOOSE(MID(TEXT(A4,"000000000.00"),3,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--LEFT(TEXT(A4,"000000000.00"))+MID(TEXT(A4,"000000000.00"),2,1)+MID(TEXT(A4,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(A4,"000000000.00"),4,1)+MID(TEXT(A4,"000000000.00"),5,1)+MID(TEXT(A4,"000000000.00"),6,1)+MID(TEXT(A4,"000000000.00"),7,1))=0,(--MID(TEXT(A4,"000000000.00"),8,1)+RIGHT(TEXT(A4,"000000000.00")))>0),"Million ","Million "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),4,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT(A4,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(A4,"000000000.00"),5,1)=0,--MID(TEXT(A4,"000000000.00"),6,1)=0),"Hundred ","Hundred "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),6,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A4,"000000000.00"),6,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))
&IF((--MID(TEXT(A4,"000000000.00"),4,1)+MID(TEXT(A4,"000000000.00"),5,1)+MID(TEXT(A4,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(A4,"000000000.00"),7,1)+MID(TEXT(A4,"000000000.00"),8,1)+MID(TEXT(A4,"000000000.00"),9,1))=0,--MID(TEXT(A4,"000000000.00"),7,1)<>0),"Thousand ","Thousand "))
&CHOOSE(MID(TEXT(A4,"000000000.00"),7,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")
&IF(--MID(TEXT(A4,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(A4,"000000000.00"),8,1)=0,--MID(TEXT(A4,"000000000.00"),9,1)=0),"Hundred ","Hundred "))&
CHOOSE(MID(TEXT(A4,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),9,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A4,"000000000.00"),9,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))

&IF(INT(A4)=0,"Zero Dollars ",IF(INT(A4)=1,"Dollar ","Dollars "))&"and "&IF(AND(MID(TEXT(A4,"000000000.00"),11,1)="0",MID(TEXT(A4,"000000000.00"),12,1)="0"),"Zero ",CHOOSE(MID(TEXT(A4,"000000000.00"),11,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A4,"000000000.00"),11,1)<>1,CHOOSE(MID(TEXT(A4,"000000000.00"),12,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A4,"000000000.00"),12,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen ")))&IF(RIGHT(TEXT(A4,"000000000.00"),2)="01","Cent","Cents"))

 

[Joe4]

I have merged your last post in with your original question on the topic.
Pursuant to forum rules, please do not start multiple threads on the same topic - just continue in your original one.

Thank you.