CODEWITHSUNDEEP
excelexcel-formula
Andy K
Andy K

Reputation: 5044

determine the cell holding the longest string with excel

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.

Upvotes: 9

Views: 21692

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

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

enter image description here

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

Upvotes: 15

Related Questions