SOLVED

Excel NumToWords Formula

Silver Contributor

To revel my 20th week as a member of this EXCELlent Community, I hereby share my NumToWords formula. Delve into it in the attached file and please apprise me of your thoughts thereon.

43 Replies

@istiakahmed 

You are welcome.

 

Just copy the formula (Conversion) and paste it next to any cell which contains the amount in numerals. 

Even if you copy it to another file it will work.

 

Thanks

Tauqeer

@Twifoo 

well  !! if we edit "=IF(CellLeft<0,"Negative ","")&IntTxt&DecTxt " and re write as  "=IF(CellLeft<0,"Negative ","")&IntTxt" then output show only figure in words and "& 0/100" not shown

@Twifoo 

Here is a simple formula just written by me for fun who are tired with VBA code:

=TRIM(LET(U,A1,V,IF(U>999999999,RIGHT(U,9),U),A,{1,"One";2,"Two";3,"Three";4,"Four";5,"Five";6,"Six";7,"Seven";8,"Eight";9,"Nine";10,"Ten";11,"Eleven";12,"Twelve";13,"Thirteen";14,"Fourteen";15,"Fifteen";16,"Sixteen";17,"Seventeen";18,"Eighteen";19,"Nineteen";20,"Twenty";21,"Twenty One";22,"Twenty Two";23,"Twenty Three";24,"Twenty Four";25,"Twenty Five";26,"Twenty Six";27,"Twenty Seven";28,"Twenty Eight";29,"Twenty Nine";30,"Thirty";31,"Thirty One";32,"Thirty Two";33,"Thirty Three";34,"Thirty Four";35,"Thirty Five";36,"Thirty Six";37,"Thirty Seven";38,"Thirty Eight";39,"Thirty Nine";40,"Forty";41,"Forty One";42,"Forty Two";43,"Forty Three";44,"Forty Four";45,"Forty Five";46,"Forty Six";47,"Forty Seven";48,"Forty Eight";49,"Forty Nine";50,"Fifty";51,"Fifty One";52,"Fifty Two";53,"Fifty Three";54,"Fifty Four";55,"Fifty Five";56,"Fifty Six";57,"Fifty Seven";58,"Fifty Eight";59,"Fifty Nine";60,"Sixty";61,"Sixty One";62,"Sixty Two";63,"Sixty Three";64,"Sixty Four";65,"Sixty Five";66,"Sixty Six";67,"Sixty Seven";68,"Sixty Eight";69,"Sixty Nine";70,"Seventy";71,"Seventy One";72,"Seventy Two";73,"Seventy Three";74,"Seventy Four";75,"Seventy Five";76,"Seventy Six";77,"Seventy Seven";78,"Seventy Eight";79,"Seventy Nine";80,"Eighty";81,"Eighty One";82,"Eighty Two";83,"Eighty Three";84,"Eighty Four";85,"Eighty Five";86,"Eighty Six";87,"Eighty Seven";88,"Eighty Eight";89,"Eighty Nine";90,"Ninety";91,"Ninety One";92,"Ninety Two";93,"Ninety Three";94,"Ninety Four";95,"Ninety Five";96,"Ninety Six";97,"Ninety Seven";98,"Ninety Eight";99,"Ninety Nine"},"Rupees "&IF(U>999999999,IF(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/10000000,0)=0,"",VLOOKUP(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/10000000,0)+0,A,2,0)&" Cr ")&IF(RIGHT(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/100000,0),2)+0=0,"",VLOOKUP(RIGHT(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/100000,0),2)+0,A,2,0)&" Lakh ")&IF(RIGHT(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/1000,0),2)+0=0,"",VLOOKUP(RIGHT(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/1000,0),2)+0,A,2,0)&" Thousand ")&IF(RIGHT(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/100,0),1)+0=0,"",VLOOKUP(RIGHT(ROUNDDOWN(LEFT(U,LEN(U)-9)*100/100,0),1)+0,A,2,0)&" Hundred ")&IF(RIGHT(ROUND(LEFT(U,LEN(U)-9)*100,0),2)+0=0,"",VLOOKUP(RIGHT(ROUND(LEFT(U,LEN(U)-9)*100,0),2)+0,A,2,0)),"")&IF(ROUNDDOWN(V/10000000,0)=0,"",VLOOKUP(ROUNDDOWN(V/10000000,0)+0,A,2,0)&" Cr ")&IF(RIGHT(ROUNDDOWN(V/100000,0),2)+0=0,"",VLOOKUP(RIGHT(ROUNDDOWN(V/100000,0),2)+0,A,2,0)&" Lakh ")&IF(RIGHT(ROUNDDOWN(V/1000,0),2)+0=0,"",VLOOKUP(RIGHT(ROUNDDOWN(V/1000,0),2)+0,A,2,0)&" Thousand ")&IF(RIGHT(ROUNDDOWN(V/100,0),1)+0=0,"",VLOOKUP(RIGHT(ROUNDDOWN(V/100,0),1)+0,A,2,0)&" Hundred ")&IF(RIGHT(ROUND(V,0),2)+0=0,"",VLOOKUP(RIGHT(ROUND(V,0),2)+0,A,2,0))&" only"))

Great work Twifoo