View previous topic :: View next topic 
Author 
Message 
jitin_21 Power User
Joined: 28 May 2007 Posts: 93 Location: New Delhi

Posted: Sat Feb 05, 2011 12:34 am Post subject: Number to Text formula shows error (Solved) 


I have written the following formula to change number to text in MSo.
=IF(E9<1000,"",IF(OR(LEFT(RIGHT(E9,5),1)*10=0,(LEFT(RIGHT(E9,5),1))*10>19),IF(LEFT(RIGHT(E9,4),1)*1=1,"One ","")&IF(LEFT(RIGHT(E9,4),1)*1=2,"Two ","")&IF(LEFT(RIGHT(E9,4),1)*1=3,"Three ","")&IF(LEFT(RIGHT(E9,4),1)*1=4,"Four ","")&IF(LEFT(RIGHT(E9,4),1)*1=5,"Five ","")&IF(LEFT(RIGHT(E9,4),1)*1=6,"Six ","")&IF(LEFT(RIGHT(E9,4),1)*1=7,"Seven ","")&IF(LEFT(RIGHT(E9,4),1)*1=8,"Eight ","")&IF(LEFT(RIGHT(E9,4),1)*1=9,"Nine ",""),"")&IF(OR(LEFT(RIGHT(E9,5),1)*1>0,LEFT(RIGHT(E9,4),1)*1>0),"Thousand ",""))&IF(E9<100,"",IF((LEFT(RIGHT(E9,3),1))*1=1,"One ","")&IF((LEFT(RIGHT(E9,3),1))*1=2,"Two ","")&IF((LEFT(RIGHT(E9,3),1))*1=3,"Three ","")&IF((LEFT(RIGHT(E9,3),1))*1=4,"Four ","")&IF((LEFT(RIGHT(E9,3),1))*1=5,"Five ","")&IF((LEFT(RIGHT(E9,3),1))*1=6,"Six ","")&IF((LEFT(RIGHT(E9,3),1))*1=7,"Seven ","")&IF((LEFT(RIGHT(E9,3),1))*1=8,"Eight ","")&IF((LEFT(RIGHT(E9,3),1))*1=9,"Nine ","")&IF((LEFT(RIGHT(E9,3),1))*1=0,"","Hundred "))&IF(E9<10,"",IF(LEFT(RIGHT(E9,2),1)*10=20,"Twenty ",""))
When i opened the same file in OOo the formula looks as follows.
=IF(E9<1000,"",IF(OR(LEFT(RIGHT(E9,5),1)*10=0,(LEFT(RIGHT(E9,5),1))*10>19),IF(LEFT(RIGHT(E9,4),1)*1=1,"One ","")&IF(LEFT(RIGHT(E9,4),1)*1=2,"Two ","")&IF(LEFT(RIGHT(E9,4),1)*1=3,"Three ","")&IF(LEFT(RIGHT(E9,4),1)*1=4,"Four ","")&IF(LEFT(RIGHT(E9,4),1)*1=5,"Five ","")&IF(LEFT(RIGHT(E9,4),1)*1=6,"Six ","")&IF(LEFT(RIGHT(E9,4),1)*1=7,"Seven ","")&IF(LEFT(RIGHT(E9,4),1)*1=8,"Eight ","")&IF(LEFT(RIGHT(E9,4),1)*1=9,"Nine ",""),"")&IF(OR(LEFT(RIGHT(E9,5),1)*1>0,LEFT(RIGHT(E9,4),1)*1>0),"Thousand ",""))&IF(E9<100,"",IF((LEFT(RIGHT(E9,3),1))*1=1,"One ","")&IF((LEFT(RIGHT(E9,3),1))*1=2,"Two ","")&IF((LEFT(RIGHT(E9,3),1))*1=3,"Three ","")&IF((LEFT(RIGHT(E9,3),1))*1=4,"Four ","")&IF((LEFT(RIGHT(E9,3),1))*1=5,"Five ","")&IF((LEFT(RIGHT(E9,3),1))*1=6,"Six ","")&IF((LEFT(RIGHT(E9,3),1))*1=7,"Seven ","")&IF((LEFT(RIGHT(E9,3),1))*1=8,"Eight ","")&IF((
I think this is related to the length of characters in the OOo cells.
Kindly help me to resolve this issue. _________________ Jitin
Using OOo2.X & 3.X on Windows or Linux
Last edited by jitin_21 on Tue Feb 08, 2011 3:01 am; edited 1 time in total 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Sat Feb 05, 2011 3:27 am Post subject: 


This produces the same results in Excel and Calc...
Code:  =IF(E9<1000,"",LOOKUP(INT(E9/1000),{0,1,2,3,4,5,6,7,8,9,10},{"","Thousand ","Two Thousand ","Three Thousand ","Four Thousand ","Five Thousand ","Six Thousand ","Seven Thousand ","Eight Thousand ","Nine Thousand "})&LOOKUP(INT((E9INT(E9/1000)*1000)/100),{0,1,2,3,4,5,6,7,8,9,10},{"","One Hundred ","Two Hundred ","Three Hundred ","Four Hundred ","Five Hundred ","Six Hundred ","Seven Hundred ","Eight Hundred ","Nine Hundred "})&LOOKUP(INT((E9INT(E9/100)*100)/10),{0,1,2,3,4,5,6,7,8,9,10},{"","","Twenty ","","","","","","",""})) 
When opened using Calc it changes to...
Code:  =IF(E9<1000;"";LOOKUP(INT(E9/1000);{0;1;2;3;4;5;6;7;8;9;10};{"";"Thousand ";"Two Thousand ";"Three Thousand ";"Four Thousand ";"Five Thousand ";"Six Thousand ";"Seven Thousand ";"Eight Thousand ";"Nine Thousand "})&LOOKUP(INT((E9INT(E9/1000)*1000)/100);{0;1;2;3;4;5;6;7;8;9;10};{"";"One Hundred ";"Two Hundred ";"Three Hundred ";"Four Hundred ";"Five Hundred ";"Six Hundred ";"Seven Hundred ";"Eight Hundred ";"Nine Hundred "})&LOOKUP(INT((E9INT(E9/100)*100)/10);{0;1;2;3;4;5;6;7;8;9;10};{"";"";"Twenty ";"";"";"";"";"";"";""})) 
Of all the tens, ie Ten, Twenty, Thirty, Forty, etc; your formula only shows Twenty.
This formula shows thousands up to nine thousand (One Thousand is shown as One Thousand, not just Thousand), hundreds up to nine hundred and all of the tens...
Code:  =IF(E9<1000,"",LOOKUP(INT(E9/1000),{0,1,2,3,4,5,6,7,8,9,10},{0,"One Thousand ","Two Thousand ","Three Thousand ","Four Thousand ","Five Thousand ","Six Thousand ","Seven Thousand ","Eight Thousand ","Nine Thousand "})&LOOKUP(INT((E9INT(E9/1000)*1000)/100),{0,1,2,3,4,5,6,7,8,9,10},{0,"One Hundred ","Two Hundred ","Three Hundred ","Four Hundred ","Five Hundred ","Six Hundred ","Seven Hundred ","Eight Hundred ","Nine Hundred "})&LOOKUP(INT((E9INT(E9/100)*100)/10),{0,1,2,3,4,5,6,7,8,9,10},{0,"Ten ","Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety "}))  which in Calc appears as...
Code:  =IF(E9<1000;"";LOOKUP(INT(E9/1000);{0;1;2;3;4;5;6;7;8;9;10};{;"One Thousand ";"Two Thousand ";"Three Thousand ";"Four Thousand ";"Five Thousand ";"Six Thousand ";"Seven Thousand ";"Eight Thousand ";"Nine Thousand "})&LOOKUP(INT((E9INT(E9/1000)*1000)/100);{0;1;2;3;4;5;6;7;8;9;10};{;"One Hundred ";"Two Hundred ";"Three Hundred ";"Four Hundred ";"Five Hundred ";"Six Hundred ";"Seven Hundred ";"Eight Hundred ";"Nine Hundred "})&LOOKUP(INT((E9INT(E9/100)*100)/10);{0;1;2;3;4;5;6;7;8;9;10};{;"Ten ";"Twenty ";"Thirty ";"Forty ";"Fifty ";"Sixty ";"Seventy ";"Eighty ";"Ninety "})) 
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


jitin_21 Power User
Joined: 28 May 2007 Posts: 93 Location: New Delhi

Posted: Sun Feb 06, 2011 11:45 pm Post subject: number to text 


I have used following formulas in:
cell:1
=IF(E9>1000000,IF(LEFT(E9,1)*10=20,"Twenty ","")&IF(LEFT(E9,1)*10=30,"Thirty ","")&IF(LEFT(E9,1)*10=40,"Forty ","")&IF(LEFT(E9,1)*10=50,"Fifty ","")&IF(LEFT(E9,1)*10=60,"Sixty ","")&IF(LEFT(E9,1)*10=70,"Seventy ","")&IF(LEFT(E9,1)*10=80,"Eighty ","")&IF(LEFT(E9,1)*10=90,"Ninety ",""),"")&IF(E9>2000000,IF(RIGHT(LEFT(E9,2),1)*1=1,"One ","")&IF(RIGHT(LEFT(E9,2),1)*1=2,"Two ","")&IF(RIGHT(LEFT(E9,2),1)*1=3,"Three ","")&IF(RIGHT(LEFT(E9,2),1)*1=4,"Four ","")&IF(RIGHT(LEFT(E9,2),1)*1=5,"Five ","")&IF(RIGHT(LEFT(E9,2),1)*1=6,"Six ","")&IF(RIGHT(LEFT(E9,2),1)*1=7,"Seven ","")&IF(RIGHT(LEFT(E9,2),1)*1=8,"Eight ","")&IF(RIGHT(LEFT(E9,2),1)*1=9,"Nine ",""),"")
cell:2
=IF(AND(E9<2000000,E9>=1000000),IF(LEFT(E9,2)*1=10,"Ten ","")&IF(LEFT(E9,2)*1=11,"Eleven ","")&IF(LEFT(E9,2)*1=12,"Twelve ","")&IF(LEFT(E9,2)*1=13,"Thirteen ","")&IF(LEFT(E9,2)*1=14,"Fourteen ","")&IF(LEFT(E9,2)*1=15,"Fifteen ","")&IF(LEFT(E9,2)*1=16,"Sixteen ","")&IF(LEFT(E9,2)*1=17,"Seventeen ","")&IF(LEFT(E9,2)*1=18,"Eighteen ","")&IF(LEFT(E9,2)*1=19,"Nineteen ",""),"")&IF(AND(E9<1000000,E9>=100000),IF(RIGHT(LEFT(E9,1),1)*1=1,"One ","")&IF(RIGHT(LEFT(E9,1),1)*1=2,"Two ","")&IF(RIGHT(LEFT(E9,1),1)*1=3,"Three ","")&IF(RIGHT(LEFT(E9,1),1)*1=4,"Four ","")&IF(RIGHT(LEFT(E9,1),1)*1=5,"Five ","")&IF(RIGHT(LEFT(E9,1),1)*1=6,"Six ","")&IF(RIGHT(LEFT(E9,1),1)*1=7,"Seven ","")&IF(RIGHT(LEFT(E9,1),1)*1=8,"Eight ","")&IF(RIGHT(LEFT(E9,1),1)*1=9,"Nine ",""),"")&IF(E9>=100000,"Lakh ","")
cell:3
=IF(E9<10000,"",IF(LEFT(RIGHT(E9,5),2)*1=11,"Eleven ","")&IF(LEFT(RIGHT(E9,5),2)*1=12,"Twelve ","")&IF(LEFT(RIGHT(E9,5),2)*1=13,"Thirteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=14,"Fourteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=15,"Fifteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=16,"Sixteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=17,"Seventeen ","")&IF(LEFT(RIGHT(E9,5),2)*1=18,"Eighteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=19,"Nineteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=10,"Ten ","")&IF(LEFT(RIGHT(E9,5),1)*10=20,"Twenty ","")&IF(LEFT(RIGHT(E9,5),1)*10=30,"Thirty ","")&IF(LEFT(RIGHT(E9,5),1)*10=40,"Forty ","")&IF(LEFT(RIGHT(E9,5),1)*10=50,"Fifty ","")&IF(LEFT(RIGHT(E9,5),1)*10=60,"Sixty ","")&IF(LEFT(RIGHT(E9,5),1)*10=70,"Seventy ","")&IF(LEFT(RIGHT(E9,5),1)*10=80,"Eighty ","")&IF(LEFT(RIGHT(E9,5),1)*10=90,"Ninety ",""))&IF(OR(E9<1000,E9>=10000),"",IF(LEFT(RIGHT(E9,5),1)*1=1,"One ",""))
cell:4
=IF(E9<1000,"",IF(OR(LEFT(RIGHT(E9,5),1)*10=0,(LEFT(RIGHT(E9,5),1))*10>19),IF(LEFT(RIGHT(E9,4),1)*1=1,"One ","")&IF(LEFT(RIGHT(E9,4),1)*1=2,"Two ","")&IF(LEFT(RIGHT(E9,4),1)*1=3,"Three ","")&IF(LEFT(RIGHT(E9,4),1)*1=4,"Four ","")&IF(LEFT(RIGHT(E9,4),1)*1=5,"Five ","")&IF(LEFT(RIGHT(E9,4),1)*1=6,"Six ","")&IF(LEFT(RIGHT(E9,4),1)*1=7,"Seven ","")&IF(LEFT(RIGHT(E9,4),1)*1=8,"Eight ","")&IF(LEFT(RIGHT(E9,4),1)*1=9,"Nine ",""),"")&IF(OR(LEFT(RIGHT(E9,5),1)*1>0,LEFT(RIGHT(E9,4),1)*1>0),"Thousand ",""))&IF(E9<100,"",IF((LEFT(RIGHT(E9,3),1))*1=1,"One ","")&IF((LEFT(RIGHT(E9,3),1))*1=2,"Two ","")&IF((LEFT(RIGHT(E9,3),1))*1=3,"Three ","")&IF((LEFT(RIGHT(E9,3),1))*1=4,"Four ","")&IF((LEFT(RIGHT(E9,3),1))*1=5,"Five ","")&IF((LEFT(RIGHT(E9,3),1))*1=6,"Six ","")&IF((LEFT(RIGHT(E9,3),1))*1=7,"Seven ","")&IF((LEFT(RIGHT(E9,3),1))*1=8,"Eight ","")&IF((LEFT(RIGHT(E9,3),1))*1=9,"Nine ","")&IF((LEFT(RIGHT(E9,3),1))*1=0,"","Hundred "))&IF(E9<10,"",IF(LEFT(RIGHT(E9,2),1)*10=20,"Twenty ",""))
cell:5
=IF(E9<10,"",IF(LEFT(RIGHT(E9,2),1)*10=30,"Thirty ","")&IF(LEFT(RIGHT(E9,2),1)*10=40,"Forty ","")&IF(LEFT(RIGHT(E9,2),1)*10=50,"Fifty ","")&IF(LEFT(RIGHT(E9,2),1)*10=60,"Sixty ","")&IF(LEFT(RIGHT(E9,2),1)*10=70,"Seventy ","")&IF(LEFT(RIGHT(E9,2),1)*10=80,"Eighty ","")&IF(LEFT(RIGHT(E9,2),1)*10=90,"Ninety ","")&IF(RIGHT(E9,2)*1=10,"Ten ","")&IF(RIGHT(E9,2)*1=11,"Eleven ","")&IF(RIGHT(E9,2)*1=12,"Twelve ","")&IF(RIGHT(E9,2)*1=13,"Thirteen ","")&IF(RIGHT(E9,2)*1=14,"Fourteen ","")&IF(RIGHT(E9,2)*1=15,"Fifteen ","")&IF(RIGHT(E9,2)*1=16,"Sixteen ","")&IF(RIGHT(E9,2)*1=17,"Seventeen ","")&IF(RIGHT(E9,2)*1=18,"Eighteen ","")&IF(RIGHT(E9,2)*1=19,"Nineteen ",""))&IF(AND(RIGHT(E9,2)*1<20,RIGHT(E9,2)*1>9),"",IF(RIGHT(E9,1)*1=1,"One ","")&IF(RIGHT(E9,1)*1=2,"Two ","")&IF(RIGHT(E9,1)*1=3,"Three ","")&IF(RIGHT(E9,1)*1=4,"Four ","")&IF(RIGHT(E9,1)*1=5,"Five ","")&IF(RIGHT(E9,1)*1=6,"Six ","")&IF(RIGHT(E9,1)*1=7,"Seven ","")&IF(RIGHT(E9,1)*1=8,"Eight ","")&IF(RIGHT(E9,1)*1=9,"Nine ",""))
i am not able to convert these formulas in OOo. @ken can u pls chk it and gv me the solution. _________________ Jitin
Using OOo2.X & 3.X on Windows or Linux 

Back to top 


jitin_21 Power User
Joined: 28 May 2007 Posts: 93 Location: New Delhi

Posted: Mon Feb 07, 2011 12:19 am Post subject: number to text 


I have used following formulas in:
cell:1
=IF(E9>1000000,IF(LEFT(E9,1)*10=20,"Twenty ","")&IF(LEFT(E9,1)*10=30,"Thirty ","")&IF(LEFT(E9,1)*10=40,"Forty ","")&IF(LEFT(E9,1)*10=50,"Fifty ","")&IF(LEFT(E9,1)*10=60,"Sixty ","")&IF(LEFT(E9,1)*10=70,"Seventy ","")&IF(LEFT(E9,1)*10=80,"Eighty ","")&IF(LEFT(E9,1)*10=90,"Ninety ",""),"")&IF(E9>2000000,IF(RIGHT(LEFT(E9,2),1)*1=1,"One ","")&IF(RIGHT(LEFT(E9,2),1)*1=2,"Two ","")&IF(RIGHT(LEFT(E9,2),1)*1=3,"Three ","")&IF(RIGHT(LEFT(E9,2),1)*1=4,"Four ","")&IF(RIGHT(LEFT(E9,2),1)*1=5,"Five ","")&IF(RIGHT(LEFT(E9,2),1)*1=6,"Six ","")&IF(RIGHT(LEFT(E9,2),1)*1=7,"Seven ","")&IF(RIGHT(LEFT(E9,2),1)*1=8,"Eight ","")&IF(RIGHT(LEFT(E9,2),1)*1=9,"Nine ",""),"")
cell:2
=IF(AND(E9<2000000,E9>=1000000),IF(LEFT(E9,2)*1=10,"Ten ","")&IF(LEFT(E9,2)*1=11,"Eleven ","")&IF(LEFT(E9,2)*1=12,"Twelve ","")&IF(LEFT(E9,2)*1=13,"Thirteen ","")&IF(LEFT(E9,2)*1=14,"Fourteen ","")&IF(LEFT(E9,2)*1=15,"Fifteen ","")&IF(LEFT(E9,2)*1=16,"Sixteen ","")&IF(LEFT(E9,2)*1=17,"Seventeen ","")&IF(LEFT(E9,2)*1=18,"Eighteen ","")&IF(LEFT(E9,2)*1=19,"Nineteen ",""),"")&IF(AND(E9<1000000,E9>=100000),IF(RIGHT(LEFT(E9,1),1)*1=1,"One ","")&IF(RIGHT(LEFT(E9,1),1)*1=2,"Two ","")&IF(RIGHT(LEFT(E9,1),1)*1=3,"Three ","")&IF(RIGHT(LEFT(E9,1),1)*1=4,"Four ","")&IF(RIGHT(LEFT(E9,1),1)*1=5,"Five ","")&IF(RIGHT(LEFT(E9,1),1)*1=6,"Six ","")&IF(RIGHT(LEFT(E9,1),1)*1=7,"Seven ","")&IF(RIGHT(LEFT(E9,1),1)*1=8,"Eight ","")&IF(RIGHT(LEFT(E9,1),1)*1=9,"Nine ",""),"")&IF(E9>=100000,"Lakh ","")
cell:3
=IF(E9<10000,"",IF(LEFT(RIGHT(E9,5),2)*1=11,"Eleven ","")&IF(LEFT(RIGHT(E9,5),2)*1=12,"Twelve ","")&IF(LEFT(RIGHT(E9,5),2)*1=13,"Thirteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=14,"Fourteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=15,"Fifteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=16,"Sixteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=17,"Seventeen ","")&IF(LEFT(RIGHT(E9,5),2)*1=18,"Eighteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=19,"Nineteen ","")&IF(LEFT(RIGHT(E9,5),2)*1=10,"Ten ","")&IF(LEFT(RIGHT(E9,5),1)*10=20,"Twenty ","")&IF(LEFT(RIGHT(E9,5),1)*10=30,"Thirty ","")&IF(LEFT(RIGHT(E9,5),1)*10=40,"Forty ","")&IF(LEFT(RIGHT(E9,5),1)*10=50,"Fifty ","")&IF(LEFT(RIGHT(E9,5),1)*10=60,"Sixty ","")&IF(LEFT(RIGHT(E9,5),1)*10=70,"Seventy ","")&IF(LEFT(RIGHT(E9,5),1)*10=80,"Eighty ","")&IF(LEFT(RIGHT(E9,5),1)*10=90,"Ninety ",""))&IF(OR(E9<1000,E9>=10000),"",IF(LEFT(RIGHT(E9,5),1)*1=1,"One ",""))
cell:4
=IF(E9<1000,"",IF(OR(LEFT(RIGHT(E9,5),1)*10=0,(LEFT(RIGHT(E9,5),1))*10>19),IF(LEFT(RIGHT(E9,4),1)*1=1,"One ","")&IF(LEFT(RIGHT(E9,4),1)*1=2,"Two ","")&IF(LEFT(RIGHT(E9,4),1)*1=3,"Three ","")&IF(LEFT(RIGHT(E9,4),1)*1=4,"Four ","")&IF(LEFT(RIGHT(E9,4),1)*1=5,"Five ","")&IF(LEFT(RIGHT(E9,4),1)*1=6,"Six ","")&IF(LEFT(RIGHT(E9,4),1)*1=7,"Seven ","")&IF(LEFT(RIGHT(E9,4),1)*1=8,"Eight ","")&IF(LEFT(RIGHT(E9,4),1)*1=9,"Nine ",""),"")&IF(OR(LEFT(RIGHT(E9,5),1)*1>0,LEFT(RIGHT(E9,4),1)*1>0),"Thousand ",""))&IF(E9<100,"",IF((LEFT(RIGHT(E9,3),1))*1=1,"One ","")&IF((LEFT(RIGHT(E9,3),1))*1=2,"Two ","")&IF((LEFT(RIGHT(E9,3),1))*1=3,"Three ","")&IF((LEFT(RIGHT(E9,3),1))*1=4,"Four ","")&IF((LEFT(RIGHT(E9,3),1))*1=5,"Five ","")&IF((LEFT(RIGHT(E9,3),1))*1=6,"Six ","")&IF((LEFT(RIGHT(E9,3),1))*1=7,"Seven ","")&IF((LEFT(RIGHT(E9,3),1))*1=8,"Eight ","")&IF((LEFT(RIGHT(E9,3),1))*1=9,"Nine ","")&IF((LEFT(RIGHT(E9,3),1))*1=0,"","Hundred "))&IF(E9<10,"",IF(LEFT(RIGHT(E9,2),1)*10=20,"Twenty ",""))
cell:5
=IF(E9<10,"",IF(LEFT(RIGHT(E9,2),1)*10=30,"Thirty ","")&IF(LEFT(RIGHT(E9,2),1)*10=40,"Forty ","")&IF(LEFT(RIGHT(E9,2),1)*10=50,"Fifty ","")&IF(LEFT(RIGHT(E9,2),1)*10=60,"Sixty ","")&IF(LEFT(RIGHT(E9,2),1)*10=70,"Seventy ","")&IF(LEFT(RIGHT(E9,2),1)*10=80,"Eighty ","")&IF(LEFT(RIGHT(E9,2),1)*10=90,"Ninety ","")&IF(RIGHT(E9,2)*1=10,"Ten ","")&IF(RIGHT(E9,2)*1=11,"Eleven ","")&IF(RIGHT(E9,2)*1=12,"Twelve ","")&IF(RIGHT(E9,2)*1=13,"Thirteen ","")&IF(RIGHT(E9,2)*1=14,"Fourteen ","")&IF(RIGHT(E9,2)*1=15,"Fifteen ","")&IF(RIGHT(E9,2)*1=16,"Sixteen ","")&IF(RIGHT(E9,2)*1=17,"Seventeen ","")&IF(RIGHT(E9,2)*1=18,"Eighteen ","")&IF(RIGHT(E9,2)*1=19,"Nineteen ",""))&IF(AND(RIGHT(E9,2)*1<20,RIGHT(E9,2)*1>9),"",IF(RIGHT(E9,1)*1=1,"One ","")&IF(RIGHT(E9,1)*1=2,"Two ","")&IF(RIGHT(E9,1)*1=3,"Three ","")&IF(RIGHT(E9,1)*1=4,"Four ","")&IF(RIGHT(E9,1)*1=5,"Five ","")&IF(RIGHT(E9,1)*1=6,"Six ","")&IF(RIGHT(E9,1)*1=7,"Seven ","")&IF(RIGHT(E9,1)*1=8,"Eight ","")&IF(RIGHT(E9,1)*1=9,"Nine ",""))
i am not able to convert these formulas in OOo. @ken can u pls chk it and gv me the solution. _________________ Jitin
Using OOo2.X & 3.X on Windows or Linux 

Back to top 


Robert Tucker Moderator
Joined: 16 Aug 2004 Posts: 3407 Location: Manchester UK


Back to top 


jitin_21 Power User
Joined: 28 May 2007 Posts: 93 Location: New Delhi

Posted: Mon Feb 07, 2011 12:52 am Post subject: 


Robert Tucker wrote:  You are aware of the extension Numbertext? 
i am aware of that but that in not giving proper result as we require....
example
1233344 one million, two hundred and thirtythree thousand, three hundred and fortyfour
we want lac in place million and need to format it..... that's y i have used that formula.... _________________ Jitin
Using OOo2.X & 3.X on Windows or Linux 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany


Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Mon Feb 07, 2011 7:08 pm Post subject: 


All I can suggest is you split each of the formulas in cells 4 and 5 (these are the ones that are too long for Calc) and use two cells for each...
http://www.mediafire.com/view/?nsr7676rmyho9rh
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
