Hi,
Can anyone please help to get the function code to convert number to word "Indian Currency")
Hi,
Can anyone please help to get the function code to convert number to word "Indian Currency")
Re: Indian Currency Number to Word
Thank you.....
Re: Indian Currency Number to Word
some times macro causes harm to your pc. try excel.
Re: Indian Currency Number to Word
Hi,
Please help me check box. I have a userform where there are several check box and a command buttons name "clear all". Here i want to unticked all the checkbox when clear all button is clicked by user.
Re: Indian Currency Number to Word
What has this got to do with the topic of this thread...?
Start another thread, please.
Re: Indian Currency Number to Word
Find and replace your selected cell from the excel formula,
=TRIM(CONCATENATE(IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{10},{1}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Crore","Twelve Crore","Thirteen Crore","Fourteen Crore","Fifteen Crore","Sixteen Crore","Seventeen Crore","Eighteen Crore","Nineteen Crore"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10},{11,1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10,11},{11,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{12,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Crore","One Crore","Two Crore","Three Crore","Four Crore","Five Crore","Six Crore","Seven Crore","Eight Crore","Nine Crore"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Lac","Twelve Lac","Thirteen Lac","Fourteen Lac","Fifteen Lac","Sixteen Lac","Seventeen Lac","Eighteen Lac","Nineteen Lac"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{11,1,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Lac","One Lac","Two Lac","Three Lac","Four Lac","Five Lac","Six Lac","Seven Lac","Eight Lac","Nine Lac"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten Thousand","Eleven Thousand","Twelve Thousand","Thirteen Thousand","Forteen Thousand","Fifteen Thousand","Sixteen Thousand","Seventeen Thousand","Eighteen Thousand","Nineteen Thousand"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="0",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}),IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),3,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Thousand","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}))),"")," ",IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"})),"")," ","Rupee"," ",IFERROR(IF(LEN(FIND(".",A12))>0,"And",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,2)="1","Ten Paisa",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Paisa","Twelve Paisa","Thirteen Paisa","Fourteen Paisa","Fifteen Paisa","Sixteen Paisa","Seventeen Paisa","Eighteen Paisa","Nineteen Paisa"}),LOOKUP(MID(A12,FIND(".",A12)+1,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Paisa","Two Paisa","Three Paisa","Four Paisa","Five Paisa","Six Paisa","Seven Paisa","Eight Paisa","Nine Paisa"})),"")," ","Only."))
Re: Indian Currency Number to Word
We have a winner!! The above is the worst formula I have ever seen.
Also;
Quotesome times macro causes harm to your pc. try excel.
That is not my experience. There are other reasons why VBA might harm your computer but more to do with a virus than anything you do personally.
Smallman
Re: Indian Currency Number to Word
nothing wrong with mega formulas ..very fast ... only they are hard to edit..
Re: Indian Currency Number to Word
vba can change your setting and do unforseen stuff to PC
Re: Indian Currency Number to Word
Will have to agree to disagree. I have a rule. If there are more than 3 criteria in a formula I start again, because there is a better way. Long formula generally slows XL performance and managing that helps create better XL files. I manage my own vb and have never had those problems. I am not talking about malicious viruses!!!
Re: Indian Currency Number to Word
hi smallman
mega forumlas are 20 to 40% faster on recalculation times
vba ten times slower
test it for yourself or http://spreadsheetpage.com/ind…p/creating_a_megaformula/
Re: Indian Currency Number to Word
That is not my experience with mega formula. My experience tells me the longer a formula the more your spreadsheet grinds to a hold. The more of these wonderful mega formula you have the slower your file. The slower your file the more useless it becomes to users. Solution - be smart about the formula you write, keep the nesting to a minimum and use helper columns where necessary.
Re: Indian Currency Number to Word
well you will be surprised they are quicker .. have a look a john walkenbacks link
Re: Indian Currency Number to Word
Sorry I don't blame VBA, just try it in excel.
its easy to use, just find and replace your desired cell.
As you better know Cntr+F and replace all( for edit).
Re: Indian Currency Number to Word
They are quciker than what? If you are talking about this formula in particular then none of my comments have been about this specific formula. My comments have been about longish formula and it is my experience that long multi nested formula is slow. That is my observation and seeing stats and explanations as to why and how won't change that. Long formula in general grinds XL to a screaming holt.
Re: Indian Currency Number to Word
test the formula in post its fast
Re: Indian Currency Number to Word
Sorry I don't blame VBA,
Just try it in excel,
As you better know CNTR+F and "Replace all" your desired cell.
Re: Indian Currency Number to Word
OK I just tested that formula. It is pretty quick - actually very quick. I am wrong about that formula. That must be very well written to be that long and not to cause spreadsheet grind.
Faysalalam
I use vba every day and I don't see it do anything it is not supposed to do.
Take care
Smallman
Don’t have an account yet? Register yourself now and be a part of our community!