Go Back

How to Find the Longest String in a 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 way.

Find the Longest String in a Column

Formula using several functions

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

Explanation of formula

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?

First, the formula to find the longest string in a 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.

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

Third, to get the position of the value with the maximum number of characters from the array, the 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, the following sheet has been considered as an example.

Figure1. Sample sheet to find the longest string in column

To find the longest string, first, 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.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need a formula to use a text string from sheet 1 as a reference to search in sheet 2 to find a number in a different column that the same text string should be found.
Solved by B. U. in 11 mins
Hi, I am having trouble using find and replace in my worksheet. I am trying to find and replace a string of text that is part of a larger portion of text in the cells of one column of the worksheet. The problem is that the find and replace function is replacing the entire cell contents, not the string of text specified in find and replace.
Solved by K. J. in 30 mins
i need to determine who had the longest stay or amount compared to other column
Solved by I. W. in 30 mins

Leave a Comment

avatar