{{ message }}

Instantly share code, notes, and snippets.

# ndthanh/English_num_to_word

Last active Jun 1, 2020
 Source: https://blog.hocexcel.online/doc-thanh-chu-bang-ham-trong-excel-bao-gom-tieng-anh-tieng-viet.html?hocexcelonline copy only formula below and enter number in cell A2 =TRIM(IF(OR(LEN(FLOOR(A2,1))=13,FLOOR(A2,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE( CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),2,1)+1,"", CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),2,1))>1, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),2,1))=0, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A2>=10^9," billion ",""), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),5,1)+1,"", CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),5,1))>1, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),5,1))=0, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),4,3))>0," million ",""), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),8,1)+1,"", CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),8,1))>1, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),8,1))=0, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),7,3))," thousand ",""), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),11,1)+1,"", CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),11,1))>1, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A2),REPT(0,12)),11,1))=0, CHOOSE(MID(TEXT(INT(A2),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A2,1)>1," dollars"," dollar"))&IF(ISERROR(FIND(".",A2,1))," and No Cents"," and "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2))=1, CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" Cents","")&CONCATENATE( CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"", CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" Cents","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1, CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine")&" Cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2)="01","one cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),1)="0", CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" Cents",""))))))))

### NewJerichoMan commented Jul 15, 2019

 How does one remove the "and Cents" portion?

### NewJerichoMan commented Jul 15, 2019

 PS - Love the use of the trim function and the proper adding of the hyphen in numbers like Nineteen Eighty-Five.

### KurtMaven commented Nov 18, 2019

 How does one remove the "and Cents" portion? I just replaced the "and Cents" with blank/space: " " Note that I also replaced the Cell Number and currency into "Pesos" =TRIM(IF(OR(LEN(FLOOR(Q2,1))=13,FLOOR(Q2,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE( CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),2,1)+1,"", CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),2,1))>1, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),2,1))=0, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(Q2>=10^9," billion ",""), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),5,1)+1,"", CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),5,1))>1, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),5,1))=0, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),4,3))>0," million ",""), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),8,1)+1,"", CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),8,1))>1, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),8,1))=0, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),7,3))," thousand ",""), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),11,1)+1,"", CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),11,1))>1, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(Q2),REPT(0,12)),11,1))=0, CHOOSE(MID(TEXT(INT(Q2),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(Q2,1)>1," Pesos"," Peso"))&IF(ISERROR(FIND(".",Q2,1))," "," and "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2))=1, CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" ","")&CONCATENATE( CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"", CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" ","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1, CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine")&" ",IF(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2)="01","one cent",IF(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),1)="0", CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Q2,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" ",""))))))))

### anikghanti commented May 17, 2020

 Problem facing when I write three, two or one decimal number like 555, 55 or 5.