Return longest string within cell

[Mlex889]

Hello Mr.Excel,

I need a way to return the longest string within a cell. (number of characters)
However, it should count strings based on line breaks within the cell.

For example,
==
This is an example
of text within one cell.
==

The above should return the value "24"

Thanks!!
Mike

 

[RedBeard]

This might be of help: Count Characters In Cell Per Line Break

 

[Mlex889]

Thanks RedBeard!!

But, I'm not sure how I can use the formulas on that thread.
I copied and pasted into Excel but it's not calculating correctly.

BTW, all line breaks within the cell are done with ALT+ENTER.

This might be of help: Count Characters In Cell Per Line Break

 

[RedBeard]

I used your example text and it worked fine with the example formula from the link I provided, first line is 20 characters, the second 24.

 

[Mlex889]

Sorry, which formula? Is there a way to attach Excel? So i can show you it's not working.

Mike

 

[RedBeard]

I put

[COLOR=#333333]=LEN(MID($A1,SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))+1,FIND(CHAR(10),$A1&CHAR(10),SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))+1)-SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))))[/COLOR]

in B1 and your text in A1.
I then dragged the formula from B1 to C1, giving me 20 in B1 and 24 in C1.

 

[Mlex889]

I put

[COLOR=#333333]=LEN(MID($A1,SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))+1,FIND(CHAR(10),$A1&CHAR(10),SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))+1)-SUMPRODUCT(N(OFFSET($A1,0,COLUMN($A1:A1)-COLUMN($A$1))))))[/COLOR]

in B1 and your text in A1.
I then dragged the formula from B1 to C1, giving me 20 in B1 and 24 in C1.

HI RedBeard,
I am putting the text in column A, and will put the formula in column B. The formula you provided does not display the number of characters in the longest string in A1 in B1. It seems to only display the number of characters in the first line of the cell.

Mike

 

[XOR LX]

Hi,

You can do this with a single array formula** if you want:

=MAX(LEN(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))))-1),LEN(A1)))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[István Hirsch]

Hi,

You can do this with a single array formula** if you want:

=MAX(LEN(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))))-1),LEN(A1)))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

The new part in the second INDEX expression shortens the processing time by some tens of percentages compared to the usual INDEX(A:A,LEN(A1)) solution.

 

[XOR LX]

The new part in the second INDEX expression shortens the processing time by some tens of percentages compared to the usual INDEX(A:A,LEN(A1)) solution.

Thanks, István. I recall now that you are something of an expert on the various forms of this construction!

Cheers