return cell address of longest text string in a range

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

Say I have a range of text strings, A1:A10.

I know I can extract the length of the longest text string in that
range by entering the array formula =MAX(LEN(A1:A10))

How can I return the cell address of the longest string?

Thanks,

Dave
 
Here are a couple options:

This:
=CELL("address",INDEX(A:A,MATCH(MAX(INDEX(LEN(A1:A10),0)),INDEX(LEN(A1:A10),0),0)))

or...a shorter way:
="A"&MATCH(MAX(INDEX(LEN(A1:A10),0)),INDEX(LEN(A1:A10),0),0)

Note_1: Because text wrap may impact the display, there are no spaces in
those formulas.

Note_2: If there are multiple entries with the same maximum length, those
formulas return the address of the first.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Yes this helps. All text string lengths in the range are unique so
note 2 doesn't apply.

Thanks.
 
Back
Top