DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,659
- Office Version
- 365
- Platform
- Windows
Can someone please help me?
I wish to spell out the numbers in a cell into words that you would use on bank cheques. I have been using these formulas, but there has just got to be a better way, perhaps using the new functions in M365.
I wish to spell out the numbers in a cell into words that you would use on bank cheques. I have been using these formulas, but there has just got to be a better way, perhaps using the new functions in M365.
MrExcel posts20.xlsx | |||||
---|---|---|---|---|---|
C | D | E | |||
3 | 32,567.84 | ||||
4 | |||||
5 | 84.00 | ||||
6 | incl "and" | Thirty Two Thousand Five Hundred and Sixty Seven | |||
7 | excl "and" | Thirty Two Thousand Five Hundred Sixty Seven | |||
8 | |||||
9 | https://www.exceldemy.com/excel-convert-number-to-words/ | ||||
Sheet31 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5 | E5 | =MOD(C3,1)*100 |
D6 | D6 | =CHOOSE(LEFT(TEXT(C3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(C3,"000000000.00"))=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),2,1)=0,--MID(TEXT(C3,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(C3,"000000000.00"))+MID(TEXT(C3,"000000000.00"),2,1)+MID(TEXT(C3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1)+MID(TEXT(C3,"000000000.00"),7,1))=0,(--MID(TEXT(C3,"000000000.00"),8,1)+RIGHT(TEXT(C3,"000000000.00")))>0)," Million and "," Million "))&CHOOSE(MID(TEXT(C3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),5,1)=0,--MID(TEXT(C3,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))&CHOOSE(MID(TEXT(C3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(C3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(C3,"000000000.00"),7,1)+MID(TEXT(C3,"000000000.00"),8,1)+MID(TEXT(C3,"000000000.00"),9,1))=0,--MID(TEXT(C3,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),8,1)=0,--MID(TEXT(C3,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) |
D7 | D7 | =CHOOSE(LEFT(TEXT(C3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(C3,"000000000.00"))=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),2,1)=0,--MID(TEXT(C3,"000000000.00"),3,1)=0)," Hundred"," Hundred "))&CHOOSE(MID(TEXT(C3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(C3,"000000000.00"))+MID(TEXT(C3,"000000000.00"),2,1)+MID(TEXT(C3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1)+MID(TEXT(C3,"000000000.00"),7,1))=0,(--MID(TEXT(C3,"000000000.00"),8,1)+RIGHT(TEXT(C3,"000000000.00")))>0)," Million "," Million "))&CHOOSE(MID(TEXT(C3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),5,1)=0,--MID(TEXT(C3,"000000000.00"),6,1)=0)," Hundred"," Hundred"))&CHOOSE(MID(TEXT(C3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(C3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(C3,"000000000.00"),7,1)+MID(TEXT(C3,"000000000.00"),8,1)+MID(TEXT(C3,"000000000.00"),9,1))=0,--MID(TEXT(C3,"000000000.00"),7,1)<>0)," Thousand "," Thousand "))&CHOOSE(MID(TEXT(C3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),8,1)=0,--MID(TEXT(C3,"000000000.00"),9,1)=0)," Hundred "," Hundred "))&CHOOSE(MID(TEXT(C3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) |