Excel: How to Find Longest String in Column


You can use the following formulas in Excel to find the longest string in a column:

Formula 1: Find Longest String in Column

=XLOOKUP(MAX(LEN(A2:A13)),LEN(A2:A13), A2:A13)

This particular formula will return the longest string in the range A2:A13.

Formula 2: Find Length of Longest String in Column

=MAX(LEN(A2:A13))

This particular formula will return the length of the longest string in the range A2:A13.

The following example shows how to use this formula in practice.

Example: Find Longest String in Column in Excel

Suppose we have the following column of names in Excel:

Suppose we would like to find the longest name in column A.

We can type the following formula into cell D1 to do so:

=XLOOKUP(MAX(LEN(A2:A13)),LEN(A2:A13), A2:A13)

The following screenshot shows how to use this formula in practice:

Excel find longest string in column

The formula returns Kendall, which represents the longest string in the range A2:A13.

Note that we can also type the following formula into cell D2 to return the length of this longest string:

=MAX(LEN(A2:A13))

The following screenshot shows how to use this formula in practice:

The formula returns 7, which represents the length of the string Kendall.

How This Formula Works

Recall the formula that we used to find the longest string in the range A2:A13:

=XLOOKUP(MAX(LEN(A2:A13)),LEN(A2:A13), A2:A13)

This formula uses the XLOOKUP function, which uses the following basic syntax:

TEXTBEFORE(lookup_value, lookup_array, return_array, …)

where:

  • lookup_value: Value to search for
  • lookup_array: The array to search
  • return_array: The array to return

By using =XLOOKUP(MAX(LEN(A2:A13)),LEN(A2:A13), A2:A13) we are able to search for the string that has the max length in the range A2:A13 and return that string from A2:A13.

Note: You can find the complete documentation for the XLOOKUP function in Excel here.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel

2 Replies to “Excel: How to Find Longest String in Column”

  1. Unfortunately, this does not work unless entered as an array formula (Ctrl-Shift-Enter). It would be wise to explain this to readers, along with the otherwise-useful formulae.

Leave a Reply

Your email address will not be published. Required fields are marked *