Just ignore me if this has been gone over in depth before...

Has anyone got a graceful calculation to turn numbers into their text equivalents (i.e. 432.58 to "Four hundred thirty-two dollars and 58 cents")?

I started with a set of calcs someone else wrote, which involved about 9 calc fields. It didn't seem like it should be that complicated, so I tried getting it down to one field. (Silly me.) The rather long calc below is what I came up with. I have one more calc ("Display Number") that just replaces the first letter with a capital for cosmetics.

This works just fine, but it's not "sexy." I don't really NEED anything better, but it bugs me that I couldn't get the calc any shorter than this. The saving grace of this calc, for me, is that there's a repeated pattern, so, f'rinstance, to change the millions clause to a billions clause, I would just have to increment all the exponents in the millions clause by three.

Something just tells me that someone out there must have something a bit cleaner.

---------------

Case(

Number > 999999999 , "Unable to display" ,

Number = "" or Number = 0 , "" ,

Choose(Int( Mod( Number , 10^(9) ) / 10^(9 - 1) ),

"" ,"one ","two ","three ","four ","five ","six ","seven ","eight ","nine ") &

Case(Int( Mod( Number , 10^(9) ) / 10^(9 - 1) ) >0 ,"hundred " ,"" ) &

Choose(Int( Mod( Number , 10^(8) ) / 10^(8 - 1) ) ,"","","twenty","thirty","forty","fifty","sixty"," seventy","eighty","ninety") &

Case(Int( Mod( Number , 10^(8) ) / 10^(8 - 1) ) > 1 and Int( Mod( Number , 10^(7) ) / 10^(7 - 1) ) <> 0, "-") &

Choose(Case(Int( Mod( Number , 10^(8) ) / 10^(8 - 1) ) <= 1, Int( Mod( Number , 10^(8) ) / 10^(7 - 1) ) , Int( Mod( Number , 10^(7) ) / 10^(7 - 1) ) ) , "","one","two","three","four","five","six","seven" ,"eight","nine"," ten" ," eleven" ," twelve" ," thirteen" ," fourteen" ," fifteen" ," sixteen" ," seventeen" ," eighteen" ," nineteen" ) &

Case(Int( Mod( Number , 10^(7) ) / 10^(7 - 1) ) > 0 ," million " ,"")

&

Choose(Int( Mod( Number , 10^(6) ) / 10^(6 - 1) ),

"" ,"one ","two ","three ","four ","five ","six ","seven ","eight ","nine ") &

Case(Int( Mod( Number , 10^(6) ) / 10^(6 - 1) ) >0 ,"hundred " ,"" ) &

Choose(Int( Mod( Number , 10^(5) ) / 10^(5 - 1) ) ,"","","twenty","thirty","forty","fifty","sixty"," seventy","eighty","ninety") &

Case(Int( Mod( Number , 10^(5) ) / 10^(5 - 1) ) > 1 and Int( Mod( Number , 10^(4) ) / 10^(4 - 1) ) <> 0 , "-") &

Choose(Case(Int( Mod( Number , 10^(5) ) / 10^(5 - 1) ) <= 1, Int( Mod( Number , 10^(5) ) / 10^(4 - 1) ) , Int( Mod( Number , 10^(4) ) / 10^(4 - 1) ) ) , "","one","two","three","four","five","six","seven" ,"eight","nine"," ten" ," eleven" ," twelve" ," thirteen" ," fourteen" ," fifteen" ," sixteen" ," seventeen" ," eighteen" ," nineteen" ) &

Case(Int( Mod( Number , 10^(4) ) / 10^(4 - 1) ) > 0 ," thousand " ,"")

&

Choose(Int( Mod( Number , 10^(3) ) / 10^(3 - 1) ),

"" ,"one ","two ","three ","four ","five ","six ","seven ","eight ","nine ") &

Case(Int( Mod( Number , 10^(3) ) / 10^(3 - 1) ) >0 ,"hundred " ,"" ) &

Choose(Int( Mod( Number , 10^(2) ) / 10^(2 - 1) ) ,"","","twenty","thirty","forty","fifty","sixty"," seventy","eighty","ninety") &

Case(Int( Mod( Number , 10^(2) ) / 10^(2 - 1) ) > 1 and Int( Mod( Number , 10^(1) ) / 1 ) <> 0 , "-" ) &

Choose(Case(Int( Mod( Number , 10^(2) ) / 10^(2 - 1) ) <= 1, Int( Mod( Number , 10^(2) / 1) ) , Int( Mod( Number , 10^(1) ) / 1 ) ) , "","one","two","three","four","five","six","seven" ,"eight","nine"," ten" ," eleven" ," twelve" ," thirteen" ," fourteen" ," fifteen" ," sixteen" ," seventeen" ," eighteen" ," nineteen" )

&

" Dollars and "

&

Case( Round( Mod( Number , 1 ) * 100 , 0 ) < 10 , "0" ) &

Round( Mod( Number , 1 ) * 100 , 0 ) & " Cents"



)


--
Shadenfroh
shadenfroh (AT) yahoo (DOT) com

In reply to o7aZa.4187$M6.302650 (AT) newsread1 (DOT) prod.itd.earthlink.net on 8/9/03
10:24 AM by Shadenfroh shadenfroh (AT) yahoo (DOT) com:

I don't know about cleaner, shorter, etc. but here is one I have (I have
others that I got from file at www.fmfiles.com, do a search for Dollars I
think it was)


Choose(Int(Int(Amount [data]/1000000)/100)
,"","One","Two","Three","Four","Five","Six","Seven ","Eight","Nine") &
If(Int(Int(Amount [data]/1000000)/100) > 0," Hundred ","") &
Choose(Int(Mod(Int(Amount [data]/1000000),100)/10),"","","Twenty ","Thirty
","Forty ","Fifty", "Sixty ","Seventy ","Eighty ","Ninety ") &
Choose(If(Mod(Int(Amount [data]/1000000),100) >= 20,Mod(Int(Amount
[data]/1000000),10), Mod(Int(Amount [data]/1000000),100)),"", "One " ,"Two "
,"Three " ,"Four " ,"Five " ,"Six " ,"Seven " ,"Eight ","Nine " ,
"Ten " ,"Eleven " ,"Twelve " ,"Thirteen " ,"Fourteen " ,"Fifteen ","Sixteen
" ,
"Seventeen " ,"Eighteen " ,"Nineteen ") & If(Int(Amount [data]/1000000) >
0,"Million ","") & Choose(Int(Int((Amount [data]-(Int(Amount [data]/1000000)
* 1000000 ))/1000)/100),"","One","Two","Three","Four","Five","Six",
"Seven","Eight","Nine") & If(Int(Int((Amount [data]-(Int(Amount
[data]/1000000) * 1000000 ))/1000)/100) > 0," Hundred ","") &
Choose(Int(Mod(Int((Amount [data]-(Int(Amount [data]/1000000) * 1000000
))/1000),100)/10),"","","Twenty ","Thirty ","Forty","Fifty ",
"Sixty ","Seventy ","Eighty ","Ninety ") & Choose(If(Mod(Int((Amount
[data]-(Int(Amount [data]/1000000) * 1000000 ))/1000),100) >=
20,Mod(Int((Amount [data]-(Int(Amount [data]/1000000) * 1000000
))/1000),10),Mod(Int((Amount [data]-(Int(Amount [data]/1000000) * 1000000
))/1000),100)),
"","One " ,"Two " ,"Three " ,"Four " ,"Five " ,"Six " ,"Seven " ,"Eight
","Nine " ,
"Ten " ,"Eleven " ,"Twelve " ,"Thirteen " ,"Fourteen " ,"Fifteen ","Sixteen
" ,
"Seventeen " ,"Eighteen " ,"Nineteen ") & If(Int((Amount [data]-(Int(Amount
[data]/1000000) * 1000000 ))/1000) > 0,"Thousand ","") &
Choose(Int(Int(Mod(Amount
[data],1000))/100),"","One","Two","Three","Four","Five","Six",
"Seven","Eight","Nine") & If(Int(Int(Mod(Amount [data],1000))/100) > 0,"
Hundred ","") &
Choose(Int(Mod(Int(Mod(Amount [data],1000)),100)/10),"","","Twenty ","Thirty
","Forty","Fifty ",
"Sixty ","Seventy ","Eighty ","Ninety ") & Choose(If(Mod(Int(Mod(Amount
[data],1000)),100) >= 20,Mod(Int(Mod(Amount
[data],1000)),10),Mod(Int(Mod(Amount [data],1000)),100)),
"","One " ,"Two " ,"Three " ,"Four " ,"Five " ,"Six " ,"Seven " ,"Eight
","Nine " ,
"Ten " ,"Eleven " ,"Twelve " ,"Thirteen " ,"Fourteen " ,"Fifteen ","Sixteen
" ,
"Seventeen " ,"Eighteen " ,"Nineteen ") & If(Amount [data] < 1,"No Dollars",
If(Amount [data] < 2,"Dollar","Dollars")) & " & " & Choose(Int(Mod((Amount
[data]-Int(Amount [data]))*100 +.00005,100)/10),"","","Twenty ","Thirty
","Forty ","Fifty","Sixty ","Seventy ","Eighty ","Ninety ") &
Choose(If(Mod((Amount [data]-Int(Amount [data]))*100 +.00005,100) >=
20,Mod((Amount [data]-Int(Amount [data]))*100 +.00005,10),Mod((Amount
[data]-Int(Amount [data]))*100 +.00005,100)),"","One " ,"Two " ,"Three "
,"Four " ,"Five " ,"Six " ,"Seven " ,"Eight ","Nine " ,"Ten " ,"Eleven "
,"Twelve " ,"Thirteen " ,"Fourteen " ,"Fifteen ","Sixteen " ,"Seventeen "
,"Eighteen " ,"Nineteen ") & If((Amount [data]-Int(Amount [data]))*100
+.00005 < 1,"No Cents", If((Amount [data]-Int(Amount [data]))*100 +.00005 <
2,"Cent","Cents"))

HTH

Lee