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

Author Message
rmh
Newbie

Joined: 30 Mar 2004
Posts: 2

 Posted: Tue Mar 30, 2004 9:37 am    Post subject: Len function on array Hi, I have just started using OpenOffice.org Calc and was trying to use some spreadsheets created by excel. There is a formula that gives me the length of the longest string in a column. This just shows #VALUE! in Calc. I tried to make this an array formula but it did not resolve the problem. The formula is {=max(len(a1:a555))}. Any suggestions? Thanks, RMH
Guest

Posted: Tue Mar 30, 2004 9:56 am    Post subject: Re: Len function on array

 rmh wrote: Hi, I have just started using OpenOffice.org Calc and was trying to use some spreadsheets created by excel. There is a formula that gives me the length of the longest string in a column. This just shows #VALUE! in Calc. I tried to make this an array formula but it did not resolve the problem. The formula is {=max(len(a1:a555))}. Any suggestions? Thanks, RMH

I just did a quick try, not using MAX(), just the LEN(). Strings in column A. In B, I put =LEN(\$A\$1:\$A\$7), copied down the 7 rows I used. The result reflects only the cell to the left, not the array.
I did the same using two columns. this time, I got the same error as you.

So the question remains.

David.
PBiela
Power User

Joined: 10 Feb 2004
Posts: 56
Location: Frankfurt/Main Germany

 Posted: Wed Mar 31, 2004 10:44 pm    Post subject: Hi rmh, I tiried something here to. The Problem is that Len accepts only one string as input. So a working but not very handy way would be to use this formula: = Max(Len(A1);Len(A2);Len(A3); ... ;Len(A554);Len(A555) Just the ... would have to be replaced by hte Len(A4) To Len(A553) calls. Somewhat unhandy. If you don't mind to have some invisible columns in your spreadsheet i suggest another way. Use one column to determine the length of the String in the same Row: e.g. Cell B1Formula Len(A1) , B2/Len(B2) this assignment is quickly done using copy and paste. Then the Max(B1:B555) works fine, even when the column B is set to hidden. Another way would using macros. I don't think that you can trick the simple len function into for an array. Hope this helped you, Peter Biela_________________ Who is John Galt? - Ayn Rand
rmh
Newbie

Joined: 30 Mar 2004
Posts: 2

 Posted: Thu Apr 01, 2004 5:40 am    Post subject: Thanks for the suggestions. I tried the one with using invisible columns for the length of the strings and then used the max function on that column. This worked quite well without a lot of extra typing. This would probably make a decent enhancement request so the LEN function worked on an array of strings similar to what Excel does. Thanks again, RMH.
 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