Amount to Words Formula

Hi,

I found this formula below to convert amounts to words for google spreadsheets and it works perfectly. But now I want to use it in excel 2007 and I get a lot of errors. I also cannot use VBA. I have tried to fix it but are unable to do so. Thanks for any help.

=if(or(isBlank(A1),not(isNumber(A1)),A1>=power(10,15)),ifError(1/0,"Error"),trim(arrayFormula(concatenate(if(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))<100,"",choose(int(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))/100)," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine") & " Hundred") & if(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)<>0,if(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))>100," And",if(A1>power(10,{15,12,9,6,3}),choose({1,2,3,4,5},"","","",""," And"),"")),"") & if(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)=0,"",if(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)<20,choose(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"),choose(int(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)/10),""," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") & if(mod(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100),10)=0,"","-" & choose(mod(mod(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100),10),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")))) & if(trunc(mod(A1,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))=0,"",choose({1,2,3,4,5}," Trillion"," Billion"," Million"," Thousand","")))) & if(A1>=2," Rand",if(A1>=1," Rand","")) & if((round(A1-trunc(A1),2)*100=0)+(A1<1),""," And") & if(round(A1-trunc(A1),2)*100=0,"",if(round(A1-trunc(A1),2)*100=1," One Cent",if(round(A1-trunc(A1),2)*100<20,choose(round(A1-trunc(A1),2)*100," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"),choose(int(round(A1-trunc(A1),2)*100/10),""," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") & if(mod(round(A1-trunc(A1),2)*100,10)=0,"","-" & choose(mod(round(A1-trunc(A1),2)*100,10),"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"))) & " Cent"))))

Google Docs cannot convert/handle the VBA, especially macro enabled files. That is why I decided to keep the file format Excel 2007 without the macros or vba and only use google docs to store it (no conversion to google doc). So I need a formula instead of vba.

Thx for you're quick reply.

I found this website, but i still cant get it to work:

http://www.mrexcel.com/forum/excel-q...mber-text.html

I have setup the references and everything but my answer/result is not the same as the one on the website.

Try this .It works for numbers below 10^12.

Please Login or Register  to view this content.

Thanks. I would have never figured that out on my own. You saved my day!

Thanks.Mark the thread solved.

Hi,

Thanks for the formula but there is a problem. Whenever the value ends in 20, 30, 40 etc. it gives an error message.

For example 234,020 does not work but any other value does work. Please help. Thanks again.

Hi,

Here is the solution :

http://stackoverflow.com/questions/1...rds-in-english

Thx again.

Thanks I have corrected formula as below.

Please Login or Register  to view this content.