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.
Copy it
=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:
Copy and paste the formula above into your worksheet.
Change the reference of the num variable.
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.