Formula for spelling out text from numbers for a Chequebook

[DRSteele]

Can someone please help me?

I trolled around the internets and found this formula, this bulky and complex monstrosity of a formula. It is certainly a wonder to behold! No disrespect meant to the author, but golly!

I wonder if we can create a better formula, perhaps one using some of the new functions available in 365. It should spell out the numbers into text, the most common purpose being creation of cheques that I can print in batch.

JRS_TD_M.xlsx
	A	B	C
27		12,489.67	67.00
28	incl "and"	Twelve Thousand Four Hundred and Eighty Nine
29	excl "and"	Twelve Thousand Four Hundred Eighty Nine
cheque

Cell Formulas
Range	Formula
C27	C27	=MOD(B27,1)*100
B28	B28	=CHOOSE(LEFT(TEXT(B27,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(B27,"000000000.00"))=0,,IF(AND(--MID(TEXT(B27,"000000000.00"),2,1)=0,--MID(TEXT(B27,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))&CHOOSE(MID(TEXT(B27,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B27,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B27,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B27,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(B27,"000000000.00"))+MID(TEXT(B27,"000000000.00"),2,1)+MID(TEXT(B27,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(B27,"000000000.00"),4,1)+MID(TEXT(B27,"000000000.00"),5,1)+MID(TEXT(B27,"000000000.00"),6,1)+MID(TEXT(B27,"000000000.00"),7,1))=0,(--MID(TEXT(B27,"000000000.00"),8,1)+RIGHT(TEXT(B27,"000000000.00")))>0)," Million and "," Million "))&CHOOSE(MID(TEXT(B27,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(B27,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(B27,"000000000.00"),5,1)=0,--MID(TEXT(B27,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))&CHOOSE(MID(TEXT(B27,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(B27,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B27,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B27,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(B27,"000000000.00"),4,1)+MID(TEXT(B27,"000000000.00"),5,1)+MID(TEXT(B27,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(B27,"000000000.00"),7,1)+MID(TEXT(B27,"000000000.00"),8,1)+MID(TEXT(B27,"000000000.00"),9,1))=0,--MID(TEXT(B27,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))&CHOOSE(MID(TEXT(B27,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(B27,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B27,"000000000.00"),8,1)=0,--MID(TEXT(B27,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&CHOOSE(MID(TEXT(B27,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B27,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B27,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B27,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
B29	B29	=CHOOSE(LEFT(TEXT(B27,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(B27,"000000000.00"))=0,,IF(AND(--MID(TEXT(B27,"000000000.00"),2,1)=0,--MID(TEXT(B27,"000000000.00"),3,1)=0)," Hundred"," Hundred "))&CHOOSE(MID(TEXT(B27,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B27,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B27,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B27,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(B27,"000000000.00"))+MID(TEXT(B27,"000000000.00"),2,1)+MID(TEXT(B27,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(B27,"000000000.00"),4,1)+MID(TEXT(B27,"000000000.00"),5,1)+MID(TEXT(B27,"000000000.00"),6,1)+MID(TEXT(B27,"000000000.00"),7,1))=0,(--MID(TEXT(B27,"000000000.00"),8,1)+RIGHT(TEXT(B27,"000000000.00")))>0)," Million "," Million "))&CHOOSE(MID(TEXT(B27,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(B27,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(B27,"000000000.00"),5,1)=0,--MID(TEXT(B27,"000000000.00"),6,1)=0)," Hundred"," Hundred"))&CHOOSE(MID(TEXT(B27,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(B27,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B27,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B27,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(B27,"000000000.00"),4,1)+MID(TEXT(B27,"000000000.00"),5,1)+MID(TEXT(B27,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(B27,"000000000.00"),7,1)+MID(TEXT(B27,"000000000.00"),8,1)+MID(TEXT(B27,"000000000.00"),9,1))=0,--MID(TEXT(B27,"000000000.00"),7,1)<>0)," Thousand "," Thousand "))&CHOOSE(MID(TEXT(B27,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(B27,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B27,"000000000.00"),8,1)=0,--MID(TEXT(B27,"000000000.00"),9,1)=0)," Hundred "," Hundred "))&CHOOSE(MID(TEXT(B27,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(B27,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B27,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B27,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

 

[lrobbo314]

I came up with one here. But, @Xlambda has a better solution if you scroll down.

 

[Macropod]

If you use mailmerge via Word, you don't need a formula, since it can be done with a simple field code switch. See Mailmerge Number & Currency Formatting in the Mailmerge Tips & Tricks thread at: Mailmerge Tips & Tricks

 

[DRSteele]

I came up with one here. But, @Xlambda has a better solution if you scroll down.

Wow, you guys really went to town!

It's better than the one I posted - thanks for the link. I ended up opting for the single-cell one.

 

[DRSteele]

If you use mailmerge via Word, you don't need a formula, since it can be done with a simple field code switch. See Mailmerge Number & Currency Formatting in the Mailmerge Tips & Tricks thread at: Mailmerge Tips & Tricks

Thanks for including the suggestion. But I need a formula within Excel.