Skip to content

Instantly share code, notes, and snippets.

@ndthanh

ndthanh/English_num_to_word

Last active August 24, 2021 17:57
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save ndthanh/3644114f4619a4a25e8a9148fc92ca27 to your computer and use it in GitHub Desktop.
Save ndthanh/3644114f4619a4a25e8a9148fc92ca27 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
English_num_to_word
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
Copy link

How does one remove the "and Cents" portion?

@NewJerichoMan
Copy link

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

@KurtMaven
Copy link

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
Copy link

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

@yieeen
Copy link

yieeen commented Apr 15, 2021

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")&" ",""))))))))

insted of words in centavos, can you make it to fraction type? (60/100 or 1/100)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment