Forum Discussion
Excel NumToWords Formula
Jay8642, your request is my command! Note that I simply edited the formulas for these defined names:
1. CurName
=IF(INT(IntNum)=0,"","Pound"&LEFT("s",INT(IntNum)>1))2. DecName
="Pen"&IF(SIGN(DecNum-1),"ce","ny")Finally, I renamed NumToDollars as NumToPounds. To satisfy your curiosity on the process of constructing the formula, you can read my articles on NumToWords and NumToDollars. The snapshot below illustrates the use of the NumToPounds formula:
The Excel file containing the NumToPounds formula is hereto attached for the benefit of those who may be interested in testing its usage.
Jay8642, your request is my command! Note that I simply edited the formulas for these defined names:
1. CurName
=IF(INT(IntNum)=0,"","Pound"&LEFT("s",INT(IntNum)>1))
2. DecName
="Pen"&IF(SIGN(DecNum-1),"ce","ny")
Finally, I renamed NumToDollars as NumToPounds. To satisfy your curiosity on the process of constructing the formula, you can read my articles on NumToWords and NumToDollars. The snapshot below illustrates the use of the NumToPounds formula:
The Excel file containing the NumToPounds formula is hereto attached for the benefit of those who may be interested in testing its usage.
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"))