CALC: Maximum Length of String in a Range of Cells

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.

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 (:heavy_check_mark:) next to the answer.

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

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.

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