Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@NewJerichoMan NewJerichoMan commented Jul 15, 2019

How does one remove the "and Cents" portion?

@NewJerichoMan

This comment has been minimized.

Copy link

@NewJerichoMan 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

This comment has been minimized.

Copy link

@KurtMaven 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

This comment has been minimized.

Copy link

@anikghanti anikghanti commented May 17, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.