[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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 LinuxLast edited by jitin_21 on Tue Feb 08, 2011 3:01 am; edited 1 time in total
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((E9-INT(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((E9-INT(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((E9-INT(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((E9-INT(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((E9-INT(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((E9-INT(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((E9-INT(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((E9-INT(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
_________________
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
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
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

 Posted: Mon Feb 07, 2011 12:22 am    Post subject: You are aware of the extension Numbertext and the macro here: http://www.oooforum.org/forum/viewtopic.phtml?t=5923_________________OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
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 thirty-three thousand, three hundred and forty-four

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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Mon Feb 07, 2011 5:09 am    Post subject: One of the rare cases where well programmed Basic code simply works across environments: http://bytes.com/topic/net/answers/786243-code-convert-currency-into-words It converts numbers(!) into Indian text with Rupees, Crore, Lakhs and Paisas. Copy the big code block into a standard module and test =RupeesToWord(A1) where A1 contains a number._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
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).
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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