Require Number to word formula in excel without VBA Code

[istiakahmed]

Hi All,

I need a formula for numbers to words without a VBA code. My currency is Taka instead of Dollars and after the decimal, it's called Paisa instead of Cent. Also require the word Only, in the last.
Here is the sample for a better understanding
1 = One Taka Only
10 = Ten Taka Only
10.50 = Ten Taka Fifty Paisa Only
100 = One Hundred Taka Only
1000 = One Thousand Taka Only
10000 = Ten Thousand Taka Only
100000 = One Lac Taka Only (We called Lac for one hundred thousand)
1000000 = Ten Lac Taka Only
10000000 = One Crore Taka Only

Please help me with this formula if anyone can able to do so.

Thanks in advance.

[#2]

@istiakahmed 

You would do well to check out this thread from three-and-a-half years ago. 

https://techcommunity.microsoft.com/t5/excel/excel-numtowords-formula/m-p/727433

You'll need to read the whole thread to see how to use the formula, and you'll want to revise it. In the meantime, I'm going to see about turning it into a LAMBDA function, since that's technology that has come along more recently.

Speaking of Lamda, what version of Excel are you using? For Lamda, you'll need to have a Microsoft 365 subscription. For LET (another capability recently released) you'll need Excel 2021 or newer.

[#3]

Hi @mathetes
Thanks for your response.
I'm currently using Microsoft 365 & 2021 both in my 2 PC.
I've checked your link already before, which is actually not solving my purpose. I've already tried with that formula and found it has some difficulties which I require.
That's why I had to open a new query with details of my requirement.

[#4]

@istiakahmed 

You'll have to be patient then. I have set it as something to work on, but I do have other things to do as well.

I also sent a message to the originator of that earlier formula asking if he possibly has updated his approach.

[#5]

which is actually not solving my purpose.

where not solving?

replace dollars with Taka?

[#6]

Hi @peiyezhu,

I was trying with this formula mention above link
https://techcommunity.microsoft.com/t5/excel/excel-numtowords-formula/m-p/727433/page/2

I cannot add taka (dollars) and Paisa (cents) and the word "only in the last of my word.

[#7 · Accepted solution]

=webservice("http://e.anyoupin.cn/eh3/?NumToBengali~" & A2)

=webservice("http://e.anyoupin.cn/eh3/?NumToBengali~10.5")

[#8]

Hi @peiyezhu,

Thanks for your response, It's working very well.
Can I know, how it's working actually !

Many thanks to your for this wonderful solution.

Regards,
Istiak Ahmed

[#9]

Here is a simple formula just written by me 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"))

[#10]

@ArindamMondal 

That works well for whole rupees but it doesn't handle paisa.

[#11]

@HansVogelaar I did not give much attention to paisa as I think its not required for most of cases. 

[#12]

@ArindamMondal 

Fair enough.