Len function on array

rmh · Newbie Joined: 30 Mar 2004 Posts: 2

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

PBiela · 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
_________________
Question

Who is John Galt? - Ayn Rand Question

rmh · Newbie Joined: 30 Mar 2004 Posts: 2

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.