Find longest string in column

If there is an array of string and there is a need to find the longest string that has the maximum number of characters in it as compared to other entries. The formula discussed in this article can be used to do this in a simpler and easier way.

Find Longest String in Column to get the longest string from a column

Formula

=INDEX(data,MATCH(MAX(LEN(data)),LEN(data),0))

Explanation

The above written generic formula makes use of Length (LEN), Maximum (MAX), MATCH and INDEX functions. A brief review of these functions and the working of the formula is discussed below.

How does the Formula to Find Longest String in Column Work?

Firstly, the formula to find the longest string in column works with the coordination of 4 formulas. The Length (LEN) function is used to return the length of each string in an array. For example, the length of the word “Old” is 3, as it contains 3 characters.

Secondly, MAX function is used to return the maximum value from an array provided to it as an argument. MAX function, with the help of LEN function, returns the number of characters of an entry in the array with the maximum length.

Thirdly, to get the position of the value with the maximum number of characters from the array, MATCH function is used. This function is configured to get the exact match by providing the 3rd parameter as 0 for this purpose. This function returns the position of that entry in the array which is passed to INDEX function as the row argument.

This INDEX function looks for that entry in the array and returns the string contained in that specific cell.

Example 

A list of states of U.S. has been considered in the form of a list in column B of Excel. To find the longest string from it following sheet has been considered as an example.

Figure1. Sample sheet to find the longest string in column

To find the longest string, firstly, find the length of each string in the array named as “states” ranging from B4 to B13. Enter the following formula in cells C4:C13.

=LEN(states)

Figure2. Finding the length of each string in the column

Now, enter the following formula in E4 to get the longest string in the column as a result:

=INDEX(states,MATCH(MAX(C4:C13),C4:C13,0))

Figure3. Entering the formula in E4 to find the longest string from array “states”, using C4:C13 as the array for Length of Strings

The result of the above-entered command can be seen in the figure below:

Figure4. The result of finding the longest string in column

It can be seen that the maximum number of string length in column C4:C13 is 13, which corresponds to New Hampshire.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar