« Lane Discipline Using Excel Custom Formatting | Hide Cell Content using CTRL + 1 (quick tip) » |

Hi there,

In this post, I will be sharing excel howto's.

How to convert Rs.100 = "One Hundred Rupees Only" through Excel Formulas?

This is our challenge for today.

Due to short of time, I am publishing only the formula in two currencies. And, I am leaving you the explanation of the formula.

**You can also download the working file.xls in the bottom of this post.**

**1. Indian Currency (Rs. = Rupees)**

** a. Crores as the base: **

...

=TRIM(IF(MOD(C6,10^15)>=(2*(10^14))," "&CHOOSE(FLOOR(MOD(C6,10^15)/10^14,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^15),10^14)>=10^13," "&CHOOSE(FLOOR(MOD(MOD(C6,10^15),10^14)/10^13,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^15)>=10^13," "&CHOOSE(FLOOR(MOD(C6,10^15)/10^13,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^15)>=10^13," Neel"," ")&IF(MOD(C6,10^13)>=(2*(10^12))," "&CHOOSE(FLOOR(MOD(C6,10^13)/10^12,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^13),10^12)>=10^11," "&CHOOSE(FLOOR(MOD(MOD(C6,10^13),10^12)/10^11,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^13)>=10^11," "&CHOOSE(FLOOR(MOD(C6,10^13)/10^11,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^13)>=10^11," Kharab"," ")&IF(MOD(C6,10^11)>=(2*(10^10))," "&CHOOSE(FLOOR(MOD(C6,10^11)/10^10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^11),10^10)>=10^9," "&CHOOSE(FLOOR(MOD(MOD(C6,10^11),10^10)/10^9,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^11)>=10^9," "&CHOOSE(FLOOR(MOD(C6,10^11)/10^9,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^11)>=10^9," Arab"," ")&IF(MOD(C6,10^9)>=200000000," "&CHOOSE(FLOOR(MOD(C6,10^9)/10^8,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^9),10^8)>=10^7," "&CHOOSE(FLOOR(MOD(MOD(C6,10^9),10^8)/10^7,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^9)>=10^7," "&CHOOSE(FLOOR(MOD(C6,10^9)/10^7,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^9)>=10^7," Crore"," ")&IF(MOD(C6,10^7)>=2000000," "&CHOOSE(FLOOR(MOD(C6,10^7)/10^6,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^7),10^6)>=10^5," "&CHOOSE(FLOOR(MOD(MOD(C6,10^7),10^6)/10^5,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^7)>=10^5," "&CHOOSE(FLOOR(MOD(C6,10^7)/10^5,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^7)>=10^5," Lakh"," ")&IF(MOD(C6,10^5)>=20000," "&CHOOSE(FLOOR(MOD(C6,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(C6,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(C6,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(MOD(C6,10^5)>=10^3," Thousand"," ")&IF(C6>=10^2," "&CHOOSE(FLOOR(MOD(C6,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),"")&IF(MOD(C6,10^3)>=10^2," Hundred"," ")&IF(MOD(C6,10^2)>=20," "&CHOOSE(FLOOR(MOD(C6,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(C6,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(C6,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(C6,10^2)>=1," "&CHOOSE(FLOOR(MOD(C6,10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(AND(C6>0.99,MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>0)," and","")&IF(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>0," Paise","")&IF(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>=20," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty", "Seventy","Eighty","Ninety")&IF(MOD(MOD(RIGHT((ROUND(C6,2))*100,2),10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(RIGHT((ROUND(C6,2))*100,2),10^2),10),1)+1,"","One","Two","Three","Four","Five","Six", "Seven","Eight","Nine"),""),IF(MOD(RIGHT((ROUND(C6,2))*100,2),10^2)>=1," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C6,2))*100,2),10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven", "Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen", "Eighteen","Nineteen"),""))&IF(C6=1," Rupee","")&IF(C6>1," Rupees","")&IF(C6>0.01," Only",""))

b. **Millions as the base**

2**. Dollar Currency ($ = Dollars)**

a. **Crores as a base**

b. **Millions as a base**:

=TRIM(IF(C14>=10^14," "&CHOOSE(FLOOR(MOD(C14,10^15)/10^14,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),"")&IF(MOD(C14,10^15)>=10^14," Hundred"," ")&" "&IF(MOD(C14,10^14)>=2*10^13," "&CHOOSE(FLOOR(MOD(C14,10^14)/10^13,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(C14,10^14),10^13)>=10^12," "&CHOOSE(FLOOR(MOD(MOD(C14,10^14),10^13)/10^12,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),IF(MOD(C14,10^14)>=10^12," "&CHOOSE(FLOOR(MOD(C14,10^14)/10^12,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&IF(MOD(C14,10^15)>=10^12," Trillion"," ")&" "&IF(C14>=10^11," "&CHOOSE(FLOOR(MOD(C14,10^12)/10^11,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),"")&IF(MOD(C14,10^12)>=10^11," Hundred"," ")&" "&IF(MOD(C14,10^11)>=2*10^10," "&CHOOSE(FLOOR(MOD(C14,10^11)/10^10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(C14,10^11),10^10)>=10^9," "&CHOOSE(FLOOR(MOD(MOD(C14,10^11),10^10)/10^9,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),IF(MOD(C14,10^11)>=10^9," "&CHOOSE(FLOOR(MOD(C14,10^11)/10^9,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&IF(MOD(C14,10^12)>=10^9," Billion"," ")&" "&IF(C14>=10^8," "&CHOOSE(FLOOR(MOD(C14,10^9)/10^8,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),"")&IF(MOD(C14,10^9)>=10^8," Hundred"," ")&" "&IF(MOD(C14,10^8)>=2*10^7," "&CHOOSE(FLOOR(MOD(C14,10^8)/10^7,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(C14,10^8),10^7)>=10^6," "&CHOOSE(FLOOR(MOD(MOD(C14,10^8),10^7)/10^6,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),IF(MOD(C14,10^8)>=10^6," "&CHOOSE(FLOOR(MOD(C14,10^8)/10^6,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&IF(MOD(C14,10^9)>=10^6," Million"," ")&" "&IF(C14>=10^5," "&CHOOSE(FLOOR(MOD(C14,10^6)/10^5,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),"")&IF(MOD(C14,10^6)>=10^5," Hundred"," ")&" "&IF(MOD(C14,10^5)>=20000," "&CHOOSE(FLOOR(MOD(C14,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(C14,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(C14,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),IF(MOD(C14,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(C14,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&IF(MOD(C14,10^6)>=10^3," Thousand"," ")&IF(C14>=10^2," "&CHOOSE(FLOOR(MOD(C14,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),"")&IF(MOD(C14,10^3)>=10^2," Hundred"," ")&IF(MOD(C14,10^2)>=20," "&CHOOSE(FLOOR(MOD(C14,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(C14,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(C14,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),IF(MOD(C14,10^2)>=1," "&CHOOSE(FLOOR(MOD(C14,10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&IF(C14=1," Dollar","")&IF(C14>1," Dollars","")&IF(AND(C14>0.99,MOD(RIGHT((ROUND(C14,2))*100,2),10^2)>0)," and","")&IF(MOD(RIGHT((ROUND(C14,2))*100,2),10^2)>=20," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C14,2))*100,2),10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(RIGHT((ROUND(C14,2))*100,2),10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(RIGHT((ROUND(C14,2))*100,2),10^2),10),1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),""),IF(MOD(RIGHT((ROUND(C14,2))*100,2),10^2)>=1," "&CHOOSE(FLOOR(MOD(RIGHT((ROUND(C14,2))*100,2),10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&IF(MOD(RIGHT((ROUND(C14,2))*100,2),10^2)=1," Cent","")&IF(MOD(RIGHT((ROUND(C14,2))*100,2),10^2)>1," Cents","")&IF(C14>0.01," Only",""))

**CHANCE TO WIN AN EXCELTOXL T-SHIRT:**

Yes, you are reading right. It's your time to explain the formula to the audience of exceltoxl and win a beautiful white-colour T-shirt with "exceltoxl" logo on it.

Please post your explanation in the comments section below and make sure you brief the above formula as simple as possible. If there are multiple explanations which seems to be awesome! then I will be providing "exceltoxl" T-Shirt to all of them.

I have T-shirts for Men and Woman. So, Gender is not a concern.

Go Ahead and Try your luck...! Answer is already here but only thing require is the explanation to the formula.

**Note:** It almost took me 3 days to complete this formula so, keeping that in mind, You can post your comments before 16th March 2015. I will announce the winner(s) on 18th Mar 2015 and tell you how to collect your goodie.

**DID YOU USE THIS FORMULA?**

Please do share your views on this post in the below comments section. If you like this post, please do share it on your social networking sites by clicking on the icons placed around the blog.

If you want to receive such updates to your email, **REGISTER **here for free.

Note: T-shirt image is taken from ideadesign.co.uk

Thanks,

You must be logged in to see the comments. Log in now!

If you have no account yet, you can register now! (It only takes a few seconds)

## Recent comments