OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

Number to Text formula shows error (Solved)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jitin_21
Power User
Power User


Joined: 28 May 2007
Posts: 93
Location: New Delhi
PostPosted: Sat Feb 05, 2011 12:34 am    Post subject: Number to Text formula shows error (Solved) Reply with quote
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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 1874
Location: Sydney, Australia
PostPosted: Sat Feb 05, 2011 3:27 am    Post subject: Reply with quote
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jitin_21
Power User
Power User


Joined: 28 May 2007
Posts: 93
Location: New Delhi
PostPosted: Sun Feb 06, 2011 11:45 pm    Post subject: number to text Reply with quote
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
View user's profile Send private message
jitin_21
Power User
Power User


Joined: 28 May 2007
Posts: 93
Location: New Delhi
PostPosted: Mon Feb 07, 2011 12:19 am    Post subject: number to text Reply with quote
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
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
PostPosted: Mon Feb 07, 2011 12:22 am    Post subject: Reply with quote
You are aware of the extension Numbertext and the macro here:

http://www.oooforum.org/forum/viewtopic.phtml?t=5923
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
Back to top
View user's profile Send private message
jitin_21
Power User
Power User


Joined: 28 May 2007
Posts: 93
Location: New Delhi
PostPosted: Mon Feb 07, 2011 12:52 am    Post subject: Reply with quote
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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10065
Location: Germany
PostPosted: Mon Feb 07, 2011 5:09 am    Post subject: Reply with quote
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 http://forum.openoffice.org
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 1874
Location: Sydney, Australia
PostPosted: Mon Feb 07, 2011 7:08 pm    Post subject: Reply with quote
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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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