Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc