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

Len function on array

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


Joined: 30 Mar 2004
Posts: 2

PostPosted: Tue Mar 30, 2004 9:37 am    Post subject: Len function on array Reply with quote

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
Back to top
View user's profile Send private message
Guest






PostPosted: Tue Mar 30, 2004 9:56 am    Post subject: Re: Len function on array Reply with quote

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.
Back to top
PBiela
Power User
Power User


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

PostPosted: Wed Mar 31, 2004 10:44 pm    Post subject: Reply with quote

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
_________________
Question Who is John Galt? - Ayn Rand Question
Back to top
View user's profile Send private message
rmh
Newbie
Newbie


Joined: 30 Mar 2004
Posts: 2

PostPosted: Thu Apr 01, 2004 5:40 am    Post subject: Reply with quote

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.
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