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.
Leave a Comment