Table of Contents
The easiest way to find the longest string in an Excel column is to use the LEN function. This will allow you to determine the number of characters in each cell in the column, and the cell with the largest number of characters will have the longest string. To use the LEN function, simply select the cell you want to check, type in “=LEN(A1)” (where A1 is the cell you are checking), and press enter. This will return a number which is the number of characters in the cell. You can then compare this number with other cells in the same column to find the one with the highest number and thus, the longest string.
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:
The formula returns Kendall, which represents the longest string in the range A2:A13.
=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 .