Indian Currency Number to Word

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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


    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;


    Quote

    some 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


    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


    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


    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


    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login