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)

**LARGE**(LEN($A$2:$A$18),

**3**),LEN($A$2:$A$18),

**0**))

**production**(the

**3rd**longest string)

**LARGE**(LEN($A$2:$A$18),

**4**),LEN($A$2:$A$18),

**0**))

**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)

**SMALL**(LEN($A$2:$A$18),

**3**),LEN($A$2:$A$18),

**0**))

**part**(the

**3rd**shortest string)

**SMALL**(LEN($A$2:$A$18),

**4**),LEN($A$2:$A$18),

**0**))

**part**

**(the**

**4th**shortest string)

*the first occurrence*of 4-character strings is shown, because the strings (

__part__,

__rama__,

__mart__) are not sorted in any way in the list.

**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.

## No comments:

## Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.