Forum Discussion
NUMBER TO WORDS
Now I want to show that numbers in words in Column parallel column B.
Which formula I use or how to do it?
Supposedly there is a tool that can do this quicker if you don't want to use VBA.
It is called https://www.extendoffice.com/download/kutools-for-excel.html, (I haven't used it myself). I just googled and the info came up.
On MsExcel someone shared the below formula
=CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(B3,"000000000.00"))=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),2,1)=0,--MID(TEXT(B3,"000000000.00"),3,1)=0)," Hundred"," Hundred "))
&CHOOSE(MID(TEXT(B3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
&IF((--LEFT(TEXT(B3,"000000000.00"))+MID(TEXT(B3,"000000000.00"),2,1)+MID(TEXT(B3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(B3,"000000000.00"),4,1)+MID(TEXT(B3,"000000000.00"),5,1)+MID(TEXT(B3,"000000000.00"),6,1)+MID(TEXT(B3,"000000000.00"),7,1))=0,(--MID(TEXT(B3,"000000000.00"),8,1)+RIGHT(TEXT(B3,"000000000.00")))>0)," Million "," Million "))
&CHOOSE(MID(TEXT(B3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(B3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),5,1)=0,--MID(TEXT(B3,"000000000.00"),6,1)=0)," Hundred"," Hundred "))
&CHOOSE(MID(TEXT(B3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")
&IF(--MID(TEXT(B3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))
&IF((--MID(TEXT(B3,"000000000.00"),4,1)+MID(TEXT(B3,"000000000.00"),5,1)+MID(TEXT(B3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(B3,"000000000.00"),7,1)+MID(TEXT(B3,"000000000.00"),8,1)+MID(TEXT(B3,"000000000.00"),9,1))=0,--MID(TEXT(B3,"000000000.00"),7,1)<>0)," Thousand "," Thousand "))
&CHOOSE(MID(TEXT(B3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(B3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),8,1)=0,--MID(TEXT(B3,"000000000.00"),9,1)=0)," Hundred "," Hundred "))&
CHOOSE(MID(TEXT(B3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(B3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
& "and " &RIGHT(TEXT(B3,"000000000.00"),2)&"/100"&" USD"Around that formula discussion is here How to CONVERT Numbers to Words with Built-in Excel Formula? - Microsoft Tech Community.
You mean: in cell B1 will show "One hundred fifty five thousand six hundred fifty five", in B2, B3 are similar, right....?
I wonder how to show it, too?
Yes, which formula should I use??
Hans's formulas do not take advantage of the LET function. I wrote three alternative formulas that use the LET function to reduce the size of the formulas, improve calculation speed, and make the formulas easier to read.
See the attached workbook.
In my performance tests, each of the three alternatives had about the same performance. I like the shortest formula the best:
=IFS(A3<0,"Referred cell value must not be negative!", A3>10^12-0.01,"Exceeds maximum which is 999999999999.99", TRUE, LET(cell, A3, use_proper_case, TRUE, text_int, TEXT(INT(cell),REPT(0,12)), dec_pt_posn, FIND(".",cell), hundreds, {"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "}, teens, {"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"}, units, {"","one","two","three","four","five","six","seven","eight","nine"}, hyph_units, {"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"}, words, TRIM(CONCATENATE( INDEX(hundreds, MID(text_int,1,1)+1),CHOOSE(MID(text_int,2,1)+1,"",INDEX(teens, MID(text_int,3,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,2,1))>1,INDEX(hyph_units, MID(text_int,3,1)+1),IF(VALUE(MID(text_int,2,1))=0,INDEX(units, MID(text_int,3,1)+1),"")),IF(cell>=10^9," billion ",""), INDEX(hundreds, MID(text_int,4,1)+1),CHOOSE(MID(text_int,5,1)+1,"",INDEX(teens, MID(text_int,6,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,5,1))>1,INDEX(hyph_units, MID(text_int,6,1)+1),IF(VALUE(MID(text_int,5,1))=0,INDEX(units, MID(text_int,6,1)+1),"")),IF(VALUE(MID(text_int,4,3))>0," million ",""), INDEX(hundreds, MID(text_int,7,1)+1),CHOOSE(MID(text_int,8,1)+1,"",INDEX(teens, MID(text_int,9,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,8,1))>1,INDEX(hyph_units, MID(text_int,9,1)+1),IF(VALUE(MID(text_int,8,1))=0,INDEX(units, MID(text_int,9,1)+1),"")),IF(VALUE(MID(text_int,7,3))," thousand ",""), INDEX(hundreds, MID(text_int,10,1)+1),CHOOSE(MID(text_int,11,1)+1,"",INDEX(teens, MID(text_int,12,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,11,1))>1,INDEX(hyph_units, MID(text_int,12,1)+1),IF(VALUE(MID(text_int,11,1))=0,INDEX(units, MID(text_int,12,1)+1),"")) )), IF(use_proper_case, PROPER(words), words) & IF(ISERROR(dec_pt_posn), IF(INT(cell)=0,""," and ")&"00/100", IF(INT(cell)=0,""," and ")&--LEFT(MID(cell,dec_pt_posn+1,2)&"00",2)&"/100") ) )Note that I included an "option" (use_proper_case) for generating either proper-case text or lower-case text.
