Formula for Converting Dollar Amount to Words

The article on Microsoft.com "How to convert a numeric value into English words in Excel" provides some VBA code for converting a dollar value into words, like you would see on checks or receipts.

I try to avoid using VBA in my spreadsheets whenever possible, and sometimes that leads to some very very long formulas. This one took some doing, but it IS possible to use a formula to convert dollar amounts less than 1 million into words without VBA. I wasn't able to extend this to numbers larger than a million because the formula exceeded the maximum length.

I use the formula in my new Receipt Template spreadsheet, where I highly doubt anyone would need to write a receipt for a value over a million.

FIRST, you need to define two named ranges:

numbers={"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"}
tens={"";"";"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"}

THEN, you can use one of the following formulas, where cell B8 contains the numeric value. Important: The value in B8 must already be rounded to the nearest cent (0.01). A value like 999.995 will mess up the formula.

Style 1: ... and ../100
=IF(OR(B8>1000000,B8<0),"Err: Out of Range",IF(B8<1000,"",IF(MOD(B8,1000000)>=100000,INDEX(numbers,TRUNC(MOD(B8,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(B8,100000)>=20000," "&INDEX(tens,TRUNC(MOD(B8,100000)/10000,0)+1)&IF(MOD(MOD(B8,100000),10000)>=1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100000),10000)/1000,0)+1),""),IF(MOD(B8,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(B8,100000)/1000,0)+1),""))&" Thousand") & IF(B8<1,"Zero Dollars",IF(MOD(B8,1000)>=100," "&INDEX(numbers,TRUNC(MOD(B8,1000)/100,0)+1)&" Hundred","")&IF(MOD(B8,100)>=20," "&INDEX(tens,TRUNC(MOD(B8,100)/10,0)+1)&IF(MOD(MOD(B8,100),10)>=1,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100),10),0)+1),""),IF(MOD(B8,100)>=1," "&INDEX(numbers,TRUNC(MOD(B8,100),0)+1),""))&"") & " and " & IF(MOD(B8,1)<0.01,"NO",ROUND(MOD(B8,1)*100,0)) & "/100")

Example: B8=123,456.78
Result: "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six and 78/100"

Style 2: ... Dollars and ... Cents

=IF(OR(B8>1000000,B8<0),"Err: Out of Range",IF(B8<1000,"",IF(MOD(B8,1000000)>=100000,INDEX(numbers,TRUNC(MOD(B8,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(B8,100000)>=20000," "&INDEX(tens,TRUNC(MOD(B8,100000)/10000,0)+1)&IF(MOD(MOD(B8,100000),10000)>=1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100000),10000)/1000,0)+1),""),IF(MOD(B8,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(B8,100000)/1000,0)+1),""))&" Thousand") & IF(B8<1,"Zero Dollars",IF(MOD(B8,1000)>=100," "&INDEX(numbers,TRUNC(MOD(B8,1000)/100,0)+1)&" Hundred","")&IF(MOD(B8,100)>=20," "&INDEX(tens,TRUNC(MOD(B8,100)/10,0)+1)&IF(MOD(MOD(B8,100),10)>=1,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100),10),0)+1),""),IF(MOD(B8,100)>=1," "&INDEX(numbers,TRUNC(MOD(B8,100),0)+1),""))&" Dollars") & IF(MOD(B8,1)>0," and"&IF(MOD(B8,1)>=0.2," "&INDEX(tens,MOD(B8,1)*10+1)&IF(MOD(MOD(B8,1)*100,10)>=0.5,"-"&INDEX(numbers,MOD(MOD(B8,1)*100,10)+1),""),IF(MOD(B8,1)*100>=1," "&INDEX(numbers,MOD(B8,1)*100+1),""))&" Cents"," and Zero Cents"))

Example: B8=123,456.78
Result: "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six Dollars and Seventy-Eight Cents"

You could create a formula in Excel that would handle much larger numbers if you split the formula into multiple cells. I suspect that the formula could be shortened further with some more effort, but I just don't care enough to do that.