Geek Answers Handbook

Define cell holding the longest row using excel

I am trying to find which cells store the longest row in an Excel spreadsheet.

I use the formula below to determine the length of the longest string

MAX(LEN(A2:A2000))

But here I am stuck, because I have no idea where to find this cell.

I do not want to enter the macro routine, because this is a one-time job. However, if you have nothing to do, well ... I am fine to follow this route.

I keep listening to your ideas.

Best.

+6
source share
1 answer

To get the address of the first long line, use:

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

with an array record ( CTRL + SHIFT + ENTER ).

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

enter image description here

UPDATE:

as follows from the comments, OP uses the French version of excel, so the following formula works:

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

with array record

+12
source

More articles:


All Articles