Do you want to find the longest string in a column in Google Sheets? Well, you’re in luck! In this article, I will share with you two formulas that will help you accomplish this task effortlessly. So, let’s dive in and explore these formulas together!
Index-Match Formula
The first formula we’ll discuss is the Index-Match formula combined with Max and Len. This formula is powerful and effective in finding the longest string in a column.
Option 1
To use this formula, simply apply the following formula in cell C2:
=index( B2:B, match(max(len(B2:B)),len(B2:B),0), 0 )
The above formula returns the longest string in column range B2:B in cell C2, which is “Ernestine.”
Now, let’s break down the formula to understand how it works!
- Syntax: INDEX(reference, row, column)
- Reference: B2:B
- Row: match(max(len(B2:B)),len(B2:B),0)
- Column: 0
The Match formula in the Row section plays a crucial role in finding the longest string in column B. It returns the relative position of the longest string in the B2:B range.
By offsetting the Index by the number of rows determined by the Match, we obtain the desired result.
But what about the Match formula? Let’s take a closer look!
- Syntax: MATCH(search_key, range, search_type)
- Search Key: max(len(B2:B))
- Range: len(B2:B)
- Search Type: 0
The len(B2:B) part calculates the length of each name in column B. The Max function then finds the maximum value in this range, which, in this case, is 9 (as shown by the highlighted cells).
The Match function matches this maximum value (search key) with the character length in the range and returns the relative position of the longest string.
Option 2
Here is another formula you can use to find the longest string in a column in Google Sheets:
=filter(B2:B,len(B2:B)=max(len(B2:B)))
This formula is much cleaner than the previous one. It filters the names based on the length of their strings. If the length of a string is equal to the maximum length in the range, it returns that value.
While this formula may sometimes return more than one value, the Index-Match formula will return only the first name.
Array Formula for Finding the Longest String in Each Column
If you want to perform the same evaluation in each column, you can rely on an array formula as an alternative to Index-Match.
In the example below, the formula in cell B8 finds the longest values in each column in the range B2:F6:
=ArrayFormula( vlookup( dmax( {column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1), {column(B1); if(,,)} ), H2:I5, 2,0 ) )
Let’s dive into the formula’s explanation:
- Vlookup Syntax: ARRAYFORMULA(VLOOKUP(search_key, range, index, [is_sorted]))
- Range: H2:I5
The helper range H2:I5 acts as the range in the Vlookup formula. Cell range I2:I5 contains all the unique strings in B2:F6, which can be manually entered or generated using the formula:
=sort(filter(unique(flatten(B2:F6)),unique(flatten(B2:F6))<>""))
In H2:H5, we have the length of the unique strings obtained from above:
=ArrayFormula(len(I2:I5))
The array formula in cell B8 returns the longest string in each column. The DMAX formula acts as the search key to search the first column in the range H2:I5 and returns the corresponding values from I2:I5.
The DMAX formula is as follows:
=ArrayFormula(dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)}))
So, now you know how to find the longest string in each column in Google Sheets. Enjoy exploring this feature and maximizing your productivity!
Thanks for reading! For more exciting tips and tricks, visit Crawlan.com.
