return cell address of longest text string in a range

[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

 

[Ron Coderre]

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

 

[Dave F]

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

Thanks.