Convert number to words in excel (ultimate)

This discussion features an excel formula that converts a given number into words. No VBA required, just functions. It is noteworthy that the formula (although, a pretty messy one) makes use of special functions such as LET and IFS. It does not support numbers greater than trillion.

Result:

Formula:

In the formula below, words in bold are variables whose values can be modified.

=LET( num,VALUE(A2), caseStyle,"s", digit,{"one","two","three","four","five","six","seven","eight","nine"}, ten,{"","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"}, teen,{"eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"}, decPos,IFERROR(SEARCH(".",num),LEN(num)+1), TrNum,IFERROR(MID(num,MAX(decPos-15,1),decPos-12-MAX(decPos-15,1)),""), BNum,IFERROR(IF(MID(num,MAX(decPos-12,1),decPos-9-MAX(decPos-12,1))="000","",MID(num,MAX(decPos-12,1),decPos-9-MAX(decPos-12,1))),""), MNum,IFERROR(IF(MID(num,MAX(decPos-9,1),decPos-6-MAX(decPos-9,1))="000","",MID(num,MAX(decPos-9,1),decPos-6-MAX(decPos-9,1))),""), ThNum,IFERROR(IF(MID(num,MAX(decPos-6,1),decPos-3-MAX(decPos-6,1))="000","",MID(num,MAX(decPos-6,1),decPos-3-MAX(decPos-6,1))),""), HNum,IFERROR(IF(MID(num,MAX(decPos-3,1),decPos-0-MAX(decPos-3,1))="000","",MID(num,MAX(decPos-3,1),decPos-0-MAX(decPos-3,1))),""), TrWord,LET(genNum,IF(TrNum="","",TRIM(VALUE(TrNum))),x,VALUE(MID(genNum,1,1)),y,VALUE(MID(genNum,2,1)),z,VALUE(MID(genNum,3,1)),IFS(LEN(genNum)=3,INDEX(digit,x)&" hundred" & IF(AND(y=0,z=0),""," and ")&IFS(y=0,IF(z=0,"",INDEX(digit,z)),y=1,IF(z=0,"ten",INDEX(teen,z)),y>1,INDEX(ten,y)&IF(z=0,"","-")&IF(z=0,"",INDEX(digit,z))),LEN(genNum)=2,IF(x=1,IF(y=0,"ten",INDEX(teen,y)), INDEX(ten,x)&IF(y=0,"","-")&IF(y=0,"",INDEX(digit,y))),LEN(genNum)=1,IF(x=0,"zero",INDEX(digit,x)),LEN(genNum)=0,"")&IF(LEN(genNum)=0,""," trillion"&LET(delim,IFERROR(VALUE(MID(num,decPos-12,12)),0),IFS(delim>99,", ",delim=0,"",delim<=99," and ")))), BWord,LET(genNum,IF(BNum="","",TRIM(VALUE(BNum))),x,VALUE(MID(genNum,1,1)),y,VALUE(MID(genNum,2,1)),z,VALUE(MID(genNum,3,1)),IFS(LEN(genNum)=3,INDEX(digit,x)&" hundred" & IF(AND(y=0,z=0),""," and ")&IFS(y=0,IF(z=0,"",INDEX(digit,z)),y=1,IF(z=0,"ten",INDEX(teen,z)),y>1,INDEX(ten,y)&IF(z=0,"","-")&IF(z=0,"",INDEX(digit,z))),LEN(genNum)=2,IF(x=1,IF(y=0,"ten",INDEX(teen,y)), INDEX(ten,x)&IF(y=0,"","-")&IF(y=0,"",INDEX(digit,y))),LEN(genNum)=1,IF(x=0,"zero",INDEX(digit,x)),LEN(genNum)=0,"")&IF(LEN(genNum)=0,""," billion"&LET(delim,IFERROR(VALUE(MID(num,decPos-9,9)),0),IFS(delim>99,", ",delim=0,"",delim<=99," and ")))), MWord,LET(genNum,IF(MNum="","",TRIM(VALUE(MNum))),x,VALUE(MID(genNum,1,1)),y,VALUE(MID(genNum,2,1)),z,VALUE(MID(genNum,3,1)), IFS( LEN(genNum)=3,INDEX(digit,x)&" hundred" & IF(AND(y=0,z=0),""," and ")&IFS(y=0,IF(z=0,"",INDEX(digit,z)),y=1,IF(z=0,"ten",INDEX(teen,z)),y>1,INDEX(ten,y)&IF(z=0,"","-")&IF(z=0,"",INDEX(digit,z))), LEN(genNum)=2,IF(x=1,IF(y=0,"ten",INDEX(teen,y)), INDEX(ten,x)&IF(y=0,"","-")&IF(y=0,"",INDEX(digit,y))), LEN(genNum)=1,IF(x=0,"zero",INDEX(digit,x)),LEN(genNum)=0,"")&IF(LEN(genNum)=0,""," million"&LET(delim,IFERROR(VALUE(MID(num,decPos-6,6)),0),IFS(delim>99,", ",delim=0,"",delim<=99," and ")))), ThWord,LET(genNum,IF(ThNum="","",TRIM(VALUE(ThNum))),x,VALUE(MID(genNum,1,1)),y,VALUE(MID(genNum,2,1)),z,VALUE(MID(genNum,3,1)), IFS(LEN(genNum)=3,INDEX(digit,x)&" hundred" & IF(AND(y=0,z=0),""," and ")&IFS(y=0,IF(z=0,"",INDEX(digit,z)),y=1,IF(z=0,"ten",INDEX(teen,z)),y>1,INDEX(ten,y)&IF(z=0,"","-")&IF(z=0,"",INDEX(digit,z))),LEN(genNum)=2,IF(x=1,IF(y=0,"ten",INDEX(teen,y)), INDEX(ten,x)&IF(y=0,"","-")&IF(y=0,"",INDEX(digit,y))),LEN(genNum)=1,IF(x=0,"zero",INDEX(digit,x)),LEN(genNum)=0,"")&IF(LEN(genNum)=0,""," thousand"&LET(delim,IFERROR(VALUE(MID(num,decPos-3,3)),0),IFS(delim>99,", ",delim=0,"",delim<=99," and ")))), HWord,LET(genNum,IF(HNum="","",TRIM(VALUE(HNum))),x,VALUE(MID(genNum,1,1)),y,VALUE(MID(genNum,2,1)),z,VALUE(MID(genNum,3,1)), IFS(LEN(genNum)=3,INDEX(digit,x)&" hundred" & IF(AND(y=0,z=0),""," and ")&IFS(y=0,IF(z=0,"",INDEX(digit,z)),y=1,IF(z=0,"ten",INDEX(teen,z)),y>1,INDEX(ten,y)&IF(z=0,"","-")&IF(z=0,"",INDEX(digit,z))), LEN(genNum)=2,IF(x=1,IF(y=0,"ten",INDEX(teen,y)), INDEX(ten,x)&IF(y=0,"","-")&IF(y=0,"",INDEX(digit,y))), LEN(genNum)=1,IF(x=0,"zero",INDEX(digit,x)),LEN(genNum)=0,"")&IF(LEN(genNum)=0,"",""&LET(delim,IFERROR(VALUE(MID(num,decPos-0,0)),0),IFS(delim>99,", ",delim=0,"",delim<=99," and ")))), fracPart,MID(num,decPos+1,LEN(num)-decPos), fracWord, IF(decPos<LEN(num)," point ","") & TEXTJOIN(" ",TRUE,IFERROR(IF(VALUE(MID(fracPart,{1,2,3,4,5,6,7,8,9,10},1))=0,"zero",INDEX(digit,VALUE(MID(fracPart,{1,2,3,4,5,6,7,8,9,10},1)))),"")), numWord, TrWord&BWord&MWord&ThWord&HWord&fracWord, IFERROR(IFS(decPos-1>15,"NOTE: The number you entered is beyond trillion. Please, modify the number and try again.", LEFT(caseStyle,1)="u",UPPER(numWord), LEFT(caseStyle,1)="l",LOWER(numWord), LEFT(caseStyle,1)="p", SUBSTITUTE(PROPER(numWord),"And","and"),LEFT(caseStyle,1)="s", SUBSTITUTE(numWord,LEFT(numWord,1),UPPER(LEFT(numWord,1)),1)), SUBSTITUTE(numWord,LEFT(numWord,1),UPPER(LEFT(numWord,1)),1)) )

How to use:

1. Copy and paste the formula above into your worksheet.
2. Change the reference of the num variable.
3. Change the case style by changing the value of the caseStyle variable. The options available for case style include:

• U for uppercase
• L for lowercase
• P for proper case
• S for sentence case

The default case style is sentence case.

Please, ensure to report any error encountered in using this formula. Thank you.

Related Links:

Convert currency to words