Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return the address of the range of plotted data on an XY scatter chart? | Charts and Charting in Excel | |||
Return a formula as text string to a cell | Excel Worksheet Functions | |||
Longest string in a column | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
How to make a cell return the formatted value in a text string (i. | Excel Worksheet Functions |