return cell address of longest text string in a range
 

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

return cell address of longest text string in a range
 

Here are a couple options:

This:
=CELL("address",INDEX(A:A,MATCH(MAX(INDEX(LEN(A1:A 10),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" wrote:

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

return cell address of longest text string in a range
 

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

Thanks.

On Jul 12, 10:30 am, Ron Coderre
wrote:
Here are a couple options:

This:
=CELL("address",INDEX(A:A,MATCH(MAX(INDEX(LEN(A1:A 10),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" wrote:
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- Hide quoted text -

- Show quoted text -