Hi all,
Just wondering how I can show the longest string in a column,
I've tried to find a formula that does this but it only shows the
number of characters, not the actual string contents. Any help would be
greatly appreciated.
Cheers.
Hi all,
Just wondering how I can show the longest string in a column,
I've tried to find a formula that does this but it only shows the
number of characters, not the actual string contents. Any help would be
greatly appreciated.
Cheers.
I get the feeling there should be a simpler way but this array formula (confirm with ctrl+shift+enter) would do it:
=INDEX(A1:A300,SUM(IF(LEN(A1:A300)=MAX(LEN(A1:A300)),ROW(A1:A300),0)))
Adjust 300 to the size of your data set as neccessary.
Edit: thinking about it, that won't work if there are two strings that both have the same/longest length, try this instead:
=INDEX(A1:A300,MIN(IF(LEN(A1:A300)=MAX(LEN(A1:A300)),ROW(A1:A300),0)))
Last edited by ragulduy; 03-13-2014 at 05:45 AM.
Hi
Maybe this formula would help. Its an array formula, thus hit Ctrl+Shift+Enter
=INDEX($A$1:$A$700,MATCH(MAX(LEN($A$1:$A$700)),LEN($A$1:$A$700),0))
Appreciate the help? CLICK *
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks