Are you looking for a way to find the longest string in each row of a table in Google Sheets? Look no further! In this article, I’ll share with you two of the best formulas to accomplish this task, one using an array formula and the other using a non-array formula. Let’s dive in!
Non-Array Formula to Find the Longest String in Each Row
To use this formula, simply insert it in cell G2 and drag the fill handle down. Here’s the formula:
=index(B2:F2,1, match(max(len(B2:F2)),len(B2:F2),0))
The index formula returns the longest string in each row by using the match formula to find the position of the longest string’s length in the range. It’s as simple as that!
Array Formula to Find the Longest String in Each Row
If you’re a fan of array formulas, this section is for you. We can use the Dmax function in combination with Vlookup to find the longest string row-wise. However, Dmax only works with structured data and numeric values. But don’t worry, we have a clever solution!
Here’s the formula:
=ArrayFormula(vlookup(dmax(transpose({B2:B6,len(B2:F6)}),sequence(rows(B2:B6),1,1),{if(,,);if(,,)}),{len(H2:H5),H2:H5},2,0))
In this formula, the virtual database created using the transpose function allows us to use Dmax effectively. The len function is used to convert the strings into numeric values that Dmax can work with. The vlookup function then retrieves the longest strings based on the values returned by Dmax.
And there you have it! Two powerful formulas to find the longest string in each row of a table in Google Sheets. Give them a try and see the results for yourself.
I hope you found this article helpful. Stay tuned for more tips and tricks on Google Sheets at Crawlan.com. Enjoy exploring the possibilities!
Similar: Find the Longest String in a Column in Google Sheets.
