Find longest or shortest text string in a column or row

To look for the longest or shortest text string in a column or row in Excel, you can use the INDEX, MATCH, LEN and MAX or MIN functions together.

How to find the longest or shortest string in a column or row in Excel?

To quickly find the longest or shortest name listed in the name range above, an INDEX, MATCH and LEN formula that incorporates the MAX or MIN function will help you in this way: The MAX or MIN function will retrieve the largest or smallest value in the range of string lengths provided by the LEN function. Then MATCH will locate the position of the largest or smallest value and INDEX will retrieve the value at the corresponding position.

Generic syntax

Find longest string: =INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0))

Find shortest string: =INDEX(range,MATCH(MIN(LEN(range)),LEN(range),0))

√ Note: These are array formulas that require you to enter with Ctrl + Shift + Enter.

To find the longest or shortest name, please copy or enter the formulas below in the cell F5 or F6, and press Ctrl + Shift + Enter to get the results:

Find longest name (cell H5):
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)),LEN(B5:B11),0))

Find shortest name (cell H6):
=INDEX(B5:B11,MATCH(MIN(LEN(B5:B11)),LEN(B5:B11),0))

Explanation of the formula

Here we use the formula below as an example:

=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)),LEN(B5:B11),0))

• LEN(B5:B11): The LEN function returns an array of the lengths of each name in B5:B11 like this: {5;5;6;3;4;8;5}.
• MAX(LEN(B5:B11)) = MAX({5;5;6;3;4;8;5}): The MAX function then returns the largest value of the array {5;5;6;3;4;8;5}, which is 8.
• MATCH(MAX(LEN(B5:B11)),LEN(B5:B11),0) = MATCH(8,{5;5;6;3;4;8;5},0): The match_type 0 forces the MATCH function to find the position of the exact number 8 in the array {5;5;6;3;4;8;5}. So, it returns 6 since the number is at the 6th position.
• INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)),LEN(B5:B11),0)) = INDEX(B5:B11,6): The INDEX function returns the 6th value in the name range B5:B11, which is Samantha.

Related functions

Related Formulas