how convert number to text in excel

[James]

Team
I want to convert numbers to text values as per Indian Currency.
Kindly help

Regards
James

 

[NARAYANK991]

Hi James ,

This has been asked earlier in this forum ; see if the answer provided is acceptable.

http://chandoo.org/forum/threads/spell-the-number.9073/

Narayan

 

[James]

Hi Narayan,
Good Afternoon, thanks for your quick response.
I copied the code in VBA in new module
And while I select SpellIndian from function option. I am getting error.
I have attached the screenshot of code copied and the insert function screenshot and error.

Kindly guide where I am going wrong.

Thanks.
James

 

[NARAYANK991]

Hi James ,

The problem is that during the forum changeover from the old platform ( bbPress ) to the current platform ( XenForo ) , a lot of unwanted changes have taken place ; the macro has become practically useless !

Download this file , and copy + paste the macro which is in this.

Narayan

 

[Debraj]

Please don't shoot me.. I swear this formula i have copied from somewhere.. Please please please forgive me for posting this formula..

=TRIM(CONCATENATE(IFERROR(LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{9,10},{1,2}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{8,9,10},{11,1,2}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{8,9,10,11},{11,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{7,8,9,10},{1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{6,7,8,9,10},{11,1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="0",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A1,0),3,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(A1,0),LOOKUP(LEN(TEXT(A1,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(".",A1))>0,"And",""),"")," ",IFERROR(IF(MID(A1,FIND(".",A1)+1,2)="1","Ten Paisa",""),"")," ",IFERROR(IF(MID(A1,FIND(".",A1)+1,1)="1",LOOKUP(MID(A1,FIND(".",A1)+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(A1,FIND(".",A1)+1,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(A1,FIND(".",A1)+1,1)="1",LOOKUP(MID(A1,FIND(".",A1)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(A1,FIND(".",A1)+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."))

 

[bobhc]

Good day Debraj
"don't shoot me", no chance just nice to see you back on the forum.

 

[NARAYANK991]

Hi Deb ,

The person who developed this formula must have thought he / she is being very clever.

Try the formula with the following value : 9,00,00,000/- and see what you get.

If you want to have a good laugh , try 10,00,010/-

................. is the word that comes to mind ; the time and effort spent in arriving at this formula could probably have been spent in doing something better. ( You can fill in the blanks yourself ).

Narayan

 

[SirJB7]

@Debraj Roy
Hi!
Would you be as kind as usual and upload a sample file with that formula placed in B1 cell please? It's 5983 char long and Activecell.Formula=<that_Nessie> at the immediate window crashes...
Regards!

 

[Debraj]

Yeah.. sure.. as requested.