Daniel Ferry of Excel Hero fame, wrote this in his ExcelHero Linkedin Blog about 4 years ago
I hope I have copied it correctly
====
"Converting numbers to written text is fairly straightforward from VBA. But I have never seen a good worksheet formula technique that does the same. I threw together a formula to do it, but it is a monster. It is included below to get you started. I am certain that are more efficient ways to do this, so let's see who comes up with the best formula.
Here are the rules:
1. A1 holds the number, which can be any INTEGER between 1 and 999999.
2. Create a formula that responds to the value of A1 and returns the ENGLISH written representation.
3. Example: forty-six million one hundred seventeen thousand five hundred twenty-seven
4. For proper English, numbers between 21 and 99 must be hyphenated.
5. Named constants and array constants can be used by the formula, but no other names or VBA.
6. No helper cells.
7. Better formulas are shorter, clearer, use less function calls.
Here is my first attempt, which proves it can be done.
It uses one named constant, three named array constants, and still comes in at 1371 characters.
The named constant is: n0: ="000000000"
The named array constants are:
nwords1: ={"","one","two","three","four","five","six","seven","eight","nine"}
nwords2: ={"","ten","eleven","twelve","thirteen","fourteen","fifteen", "sixteen","seventeen", "eighteen","nineteen"}
nwords3: ={"","","twenty","thirty","forty","fifty","sixty","seventy", "eighty","ninety"}
And, drum roll, here's the beast:
=REPT(INDEX(nwords1,MID(TEXT(A1,n0),1,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),1,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),1,3)>99)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),2,2)>19)*MID(TEXT(A1,n0),2,1)+1)&REPT("-",(--MID(TEXT(A1,n0),2,2)>20)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),2,2)-8)*(--MID(TEXT(A1,n0),2,2)>9)*(--MID(TEXT(A1,n0),2,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),3,1)+1)*((--MID(TEXT(A1,n0),2,2)<10)+(--MID(TEXT(A1,n0),2,2)>19)))&" million ",(LEN(A1)>6)) & REPT(INDEX(nwords1,MID(TEXT(A1,n0),4,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),4,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),4,3)>99)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),5,2)>19)*MID(TEXT(A1,n0),5,1)+1)&REPT("-",(--MID(TEXT(A1,n0),5,2)>20)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),5,2)-8)*(--MID(TEXT(A1,n0),5,2)>9)*(--MID(TEXT(A1,n0),5,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),6,1)+1)*((--MID(TEXT(A1,n0),5,2)<10)+(--MID(TEXT(A1,n0),5,2)>19)))&" thousand ",(LEN(A1)>3)) & INDEX(nwords1,(LEN(A1)>2)*LEFT(RIGHT(A1,3),1)+1)&REPT(" hundred",LEN(A1)>2)&REPT(" ",(LEN(A1)>2)*(--(RIGHT(A1,1))>0))&INDEX(nwords3,(--RIGHT(A1,2)>19)*LEFT(RIGHT(A1,2),1)+1)&REPT("-",(--RIGHT(A1,2)>20)*(--RIGHT(A1,1)>0))&INDEX(nwords2,((RIGHT(A1,2)-8)*(--RIGHT(A1,2)>9)*(--RIGHT(A1,2)<20)))&INDEX(nwords1,(RIGHT(A1,1)+1)*((--RIGHT(A1,2)<10)+(--RIGHT(A1,2)>19)))"
I believe there is an example at:
http://excelhero.com/number_words/number_words.xls
There was a good discussion in the Linkedin group where a few people shortened it a little bit, down to around 500 characters I believe
You can read it all here:
https://www.linkedin.com/groups/3843467/3843467-78700058
Good luck