In a table in Google Sheets, I want to find the longest string in each row. What are the options available?

If this is your question, I have a couple of formulas to offer.

In this post, get two of the best formulas to find the longest string in each row – One array formula and one non-array formula.

There are five rows in the below table in range B2:F6. In G2:G6, you can find the longest string in each row corresponding to them.

To get that, we can use Match (non-array) or Dmax (array) formulas.

Note:- Additionally, we will use Index and Vlookup with the former and latter formulas, respectively.

Let’s go to them one by one.

Non-Array Formula to Find the Longest String in Each Row

Formula (insert in cell G2, then copy-paste down):

=index(B2:F2,1, match(max(len(B2:F2)),len(B2:F2),0))

You can insert this formula in cell G2 and drag the fill handle in cell G2 down.

To see the fill handle, click on cell G2. Then click and drag the tiny blue-square at the lower-right corner, like this:

Formula Explanation

To learn the above formula, you should test the individual formulas used in the combination.

While doing so, use ArrayFormula, Index (without using offset arguments), or Sort with them because they won’t work standalone.

1. The Len returns the length of the strings in B2:F2 (it returns an array result).

=index(len(B2:F2))

2. Returns the max length of the strings in B2:F2 (it returns a single value).

=index(max(len(B2:F2)))

3. The Match uses the point#2 formula as the search key and piont#1 as the range to search.

Then returns the relative position of the point#2 value in the point#1 array.

=index(match(max(len(B2:F2)),len(B2:F2),0))

4. The Index (final formula) then offsets that many numbers of cells in B2:F2.

It returns the longest string in that row.

Note:- In points 1 to 3, the Index doesn’t offset any values. It just helps the inner formula to use an array as an argument.

Array Formula to Find the Longest String in Each Row

This section is for array formula lovers.

We can use the database function Dmax (with Vlookup as a combination) to find the longest string row-wise.

As you may already know, the Dmax in Sheets only works with structured data.

Our above data is in no way structured as it doesn’t have field labels. Also, Dmax is for working with numeric values.

The numeric part we can solve with the Len function, similar to our first example.

What about field labels?

We can use virtual field labels.

Let’s learn below how to use Dmax to find the longest string in each row in Google Sheets.

Formula (insert in cell G2, do not copy-paste down):

=ArrayFormula(vlookup(dmax(transpose({B2:B6,len(B2:F6)}),sequence(rows(B2:B6),1,1),{if(,,);if(,,)}),{len(H2:H5),H2:H5},2,0))

Notes:-

1. Scroll down to screenshot#4. See the helper range H2:H5. We require that range for the above formula to return the longest string in each row.

2. In H2:H5, we should enter the unique strings in B2:F6. I’ll explain more about that later.

Unlike our first example, here, there is no need to drag the blue-squared fill handle down.

The only thing you should take care of is to select and delete any (hidden) values/formulas in the range G3:G6.

This way, we can ensure that the formula in cell G2 doesn’t return the REF error.

Formula Explanation

Let’s learn the formula part by part.

See the bold part in the above formula, which is the Dmax. We will learn that part first.

dmax(transpose({B2:B6,len(B2:F6)}),sequence(rows(B2:B6),1,1),{if(,,);if(,,)})

Syntax:- DMAX(database, field, criteria)

Database

To learn the above Dmax formula that ‘helps’ to return the longest string in each row, you should know three key points, which I have already mentioned above.

What are they?

1. Dmax won’t work with strings.

2. It is not capable of returning max values in rows.

3. It requires field labels (structured data).

We will meet all these requirements cleverly as follows.

1. Use Len(B2:F6) instead of B2:F6.

2. Change the orientation of the data using Transpose. I mean change Len(B2:F6) to Transpose(Len(B2:F6)).

3. Add cell range B2:B6 within Transpose using Curly Brackets.

transpose({B2:B6,len(B2:F6)})

So when transposing, it, i.e., cell range B2:B6 will act as the field labels.

When testing, use Index, ArrayFormula, or Sort. Here I am Using ArrayFormula with it.

The above virtual database is the key to finding the longest string in each row using Dmax in Google Sheets.

Field

When you check the screenshot above, you can find that there are five fields (B, C, D, E, and F). To specify that dynamically, we can use the following Sequence formula.

sequence(rows(B2:B6),1,1)

It returns field numbers vertically, so the Dmax output will also be vertical.

Here is an alternative non-dynamic version.

{1;2;3;4;5}

Criteria

We don’t want to use any criteria. But in Dmax, we must at least specify two blank cells vertically. The below IF logical tests will take care of that.

{if(,,);if(,,)})

The Dmax will return the length of the longest string in each row, not the longest string itself.

The Vlookup helps us to return the longest string row-wise using the Dmax formula output.

How?

Vlookup Part

Syntax:- VLOOKUP(search_key, range, index, [is_sorted])

Search Key – The above Dmax returns the search keys.

Range – Please see the helper range H2:H5 on the screenshot above. It’s a list containing the unique strings in B2:F6.

Note:- You can either manually enter it or use =sort(filter(unique(flatten(B2:F6)),unique(flatten(B2:F6))<>"")) in cell H2.

We will use the below formula as the range.

{len(H2:H5),H2:H5}

The first array contains the length of the unique strings.

Index – 2

The Vlookup searches the Dmax result, i.e., the length of the longest string in each row, in the first array in the above range.

Then returns the corresponding strings from the second array.

That’s all.

I hope you could learn how to find the longest string in each row using array/non-array formulas in Google Sheets.

Thanks for the stay. Enjoy!

Similar: Find the Longest String in a Column in Google Sheets.