determine the cell holding the longest string with excel

Question

I'm trying to find which, cells hold the longest string in an excel spreadsheet.

I'm using the formula below to determine the length of the longest string

MAX(LEN(A2:A2000)) 

But here I'm stuck because I have no idea where to find this cell.

I don't want to go into a macro routine because it is a one-off job. However, if there is nothing else to do, well ... I'm ok to follow that route.

I'm all ears to your ideas.

Best.

Answer 1

To get address of first longest string use:

 =CELL("address",INDEX(A2:A2000,MATCH(MAX(LEN(A2:A2000)),LEN(A2:A2000),0)))

with array entry (CTRL+SHIFT+ENTER).

• MAX(LEN(A2:A2000)) returns max len
• MATCH(MAX(LEN(A2:A2000)),LEN(A2:A2000),0) finds index of first cell with max len in range A2:A2000
• INDEX(A2:A2000,MATCH(...) returns reference to first string with max len
• CELL("address",INDEX(..)) retrieves address from cell reference

UPDATE:

as follow up from comments, OP uses French excel version, so next formula working:

=CELLULE("adresse";INDEX(B1:B100;EQUIV(MAX(NBCAR(B1:B100));NBCAR(B1:B100);0)))

with array entry