yogi_Spell Out An Amount Say Currency Such As Dollar And Cents As Would Be On A Check -- For Google New Sheets

             Google Spreadsheet   Post  #1900

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-20-2015

Note:

formula in this blog post is for Google New Sheets 

Earlier in my following blog post I had formulation that worked in so called Google Old Sheets

yogi_Spell Out An Amount Say Currency Such As Dollar And Cents As Would Be On A Check

http://yogi--anand-consulting.blogspot.com/2012/11/yogispell-out-dollar-and-cents-as-would.html instead of trouble shooting and fixing the formula i rewrote it as presented in this blog post question by Mitrih:

https://productforums.google.com/forum/#!mydiscussions/docs/kNTxdJA_QEI
how do I get a cell reflect "eight" when the formula is 4+4 instead of =8
how do I get a cell reflect "eight" when the formula is 4+4 instead of =8
---

This might be a better explanation.

The following formula =

(IF(OR(H3>1000000,H3<=0),"",IF(H3<1000,"",IF(MOD(H3,1000000)>=100000,INDEX(TRUNC(MOD(H3,1000000)/100000,0))&" Hundred","")&IF(MOD(H3,100000)>=20000," "&INDEX(TRUNC(MOD(H3,100000)/10000,0))&IF(MOD(MOD(H3,100000),10000)>=1000,"-"&INDEX(TRUNC(MOD(MOD(H3,100000),10000)/1000,0)),""),IF(MOD(H3,100000)>=1000," "&INDEX(TRUNC(MOD(H3,100000)/1000,0)),""))&" Thousand") & IF(H3<1,"Zero Dollars",IF(MOD(H3,1000)>=100," "&INDEX(TRUNC(MOD(H3,1000)/100,0))&" Hundred",""

...

Here is the the formula in excel:

=IF(OR(H3>1000000,H3<=0),"",IF(H3<1000,"",IF(MOD(H3,1000000)>=100000,INDEX(numbers,TRUNC(MOD(H3,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(H3,100000)>=20000," "&INDEX(tens,TRUNC(MOD(H3,100000)/10000,0)+1)&IF(MOD(MOD(H3,100000),10000)>=1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(H3,100000),10000)/1000,0)+1),""),IF(MOD(H3,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(H3,100000)/1000,0)+1),""))&" Thousand") & IF(H3<1,"Zero Dollars",IF(MOD(H3,1000)>=100," "&INDEX(numbers,TRUNC(MOD(H3,1000)/100,0)+1)&" Hundred","")&IF(MOD(H3,100)>=20," "&INDEX(tens,TRUNC(MOD(H3,100)/10,0)+1)&IF(MOD(MOD(H3,100),10)>=1,"-"&INDEX(numbers,TRUNC(MOD(MOD(H3,100),10),0)+1),""),IF(MOD(H3,100)>=1," "&INDEX(numbers,TRUNC(MOD(H3,100),0)+1),""))&"") & " and " & IF(MOD(H3,1)<0.005,"NO",ROUND(MOD(H3,1)*100,0)) & "/100 -----------")

See attached file for what I am trying to achieve. When I fill the amount box with $550.25 the amount written comes as "5 Hundred 5 and 25/100 -----------" instead of "Five Hundred and Fifty Dollars and 25/100-----------"

The attached file depicts the use of the formula in my previous post.

I hope this is more clear

Attachments (1)

Screenshot 2015-02-24 at 10.57.07 PM.png

29 KB   View   Download
------------------------------------