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