How to retrieve some of the LONGEST/SHORTEST TEXT entries from Excel list

Suppose you want to find the longest or shortest entry from a long list of text strings. Or, maybe you need to retrieve the 2nd, or 3rd or 4th longest/shortest entry from the list. How to do it?

Let's start with this small fragment of a big table, for example:

The first column of this table contains the subject list of arbitrary textual entries. All other columns are provided for explanatory purpose only.

If all, what you need, is just finding the longest or shortest entry in the list, then you can use the following ARRAY formulas to find both the longest and the shortest entry:

=INDEX($A$2:$A$18,MATCH(MAX(LEN($A$2:$A$18)),LEN($A$2:$A$18),0))    Result:  composition

=INDEX($A$2:$A$18,MATCH(MIN(LEN($A$2:$A$18)),LEN($A$2:$A$18),0))    Result:  nob

As you can see, the list contains two entries (composition, participant) of the maximum length (11 characters), but the formula finds the first matching value equal exactly to the lookup value, because the strings are not sorted in any way and the formula uses 0 match-type (the strings are and can be in any order).

In the second case (smallest strings) there is only one 3-character entry (nob), so it is the first matching value by default.

This is pretty obvious. But, what if you'd like to retrieve the 2nd, 3rd, 4th, etc. longest or shortest textual entries?

Well, in such cases you'd need to use slightly modified ARRAY formulas that are shown below, first three for the longest strings and the last three for the shortest strings:

=INDEX($A$2:$A$18,MATCH(LARGE(LEN($A$2:$A$18),2),LEN($A$2:$A$18),1))    Result:  participant (the 2nd longest string; 1 requires ascending sort order of entries, and composition, participant are, in fact, in ascending order in the list)

=INDEX($A$2:$A$18,MATCH(LARGE(LEN($A$2:$A$18),3),LEN($A$2:$A$18),0))

 Result:  production (the 3rd longest string)

  

=INDEX($A$2:$A$18,MATCH(LARGE(LEN($A$2:$A$18),4),LEN($A$2:$A$18),0))

 Result:  let it be (the 4th longest string)

------------------------------------------------------

=INDEX($A$2:$A$18,MATCH(SMALL(LEN($A$2:$A$18),2),LEN($A$2:$A$18),0))    Result:  part (the 2nd shortest string)

=INDEX($A$2:$A$18,MATCH(SMALL(LEN($A$2:$A$18),3),LEN($A$2:$A$18),0))

 Result:  part (the 3rd shortest string)

  

=INDEX($A$2:$A$18,MATCH(SMALL(LEN($A$2:$A$18),4),LEN($A$2:$A$18),0))

 Result:  part (the 4th shortest string)

 

In all three cases of the shortest strings, only the first occurrence of 4-character strings is shown, because the strings (part, rama, mart) are not sorted in any way in the list.

Similarly, Barbara and Marta strings are repeated as the results of subsequent formulas, for the same reason (unsorted order in the list).

When using this type of formulas you must remember that:

1. The formulas are of ARRAY type so, after typing in, you have to enter them using CTRL+SHIFT+ENTER keyboard sequence.

2. Any spaces included in the entries are counted with LEN function, so pay attention to that.

3. If there are multiple longest or shortest entries in the list, the formulas will find just the first one. To find out, if there are more than one Max/Min length entries, you'd need to use Find feature provided in Excel ribbon.

4. The formulas do not work with multiple columns.