Ask Your Question
0

CALC: Maximum Length of String in a Range of Cells

asked 2020-05-28 15:42:24 +0100

Ales gravatar image

I have a very long column of strings and I need to find out what is the maximum length.

I thought that this will do the trick: =MAX(LEN(A1:A999)) but it doesn't work --> #VALUE.

In fact even this alone fails: =LEN(A1:A999) as well as: =SUM(LEN(A1:A999)).

What is the right way to do it?

Example:

[     A     ]
[1] aaa
[2] bbbb
[3] ccccc 
---> MAX(LEN(A1:A3)) = 5

LibreOffice Calc 6.4.3.2 (x64), win10.

Thanks.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2020-05-28 15:50:06 +0100

Opaque gravatar image

Hello,

two options:

  • force array evaluation using =SUMPRODUCT(MAX(LEN(A1:A999)))
  • enter array formula using =MAX(LEN(A1:A999)) but finalize with CTRL+SHIFT+ENTER instead of just ENTER and make it appear as {=MAX(LEN(A1:A999))}

Hope that helps.

If the answer helped to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more
0

answered 2020-05-28 16:22:40 +0100

JPGoem gravatar image

LEN(A1:A3) is not the correct syntax. LEN has only one argument (single cell) . Writing LEN(A1:A3) returns the length of cell Ax, where x is the row where the LEN function is written ! You could try to add a new column AA, where each row AAx has =LEN(Ax), and then MAX(AA1:AA999).

edit flag offensive delete link more

Comments

That's why you need to use array function and then =LEN(A1:A3) is valid syntax, if you finalize the input using CTRL+SHIFT+ENTER (getting {=LEN(A1:A3)}) and yields an array containing {3;4;5} - No need for an extra column; just test my answer.

Opaque gravatar imageOpaque ( 2020-05-28 16:30:02 +0100 )edit

Thanks but it still needs that MAX function as shown below. It seems that {=LEN(A1:A3)} alone gets the whole array.

Ales gravatar imageAles ( 2020-05-29 07:44:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-05-28 15:42:24 +0100

Seen: 506 times

Last updated: May 28 '20