To find the longest string in a column in Google Sheets, we can use Index-Match or Filter-based formulas.

Both the formulas have their plus and minuses. I will explain that later. First of all, let’s try to understand the scenario.

It’s all about finding strings based on the maximum number of characters.

Assume I have a list of names in the range B2:B in Google Sheets.

I want to return the name that has the maximum number of characters.

Two Formulas to Find the Longest String in a Column in Google Sheets

We will start with Index-Match in combination with Max and Len.

Option 1

Apply the following formula in cell C2.

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

The above index-match formula returns the longest string in column range B2:B in cell C2, which is “Ernestine.”

Can you explain it?

Why not? Here you go!

Syntax:-

INDEX(
     reference,
     row,
     column
)

Reference – B2:B

Row – match(max(len(B2:B)),len(B2:B),0)

Column – 0

The above Match formula (Row) plays a vital role in finding the longest string in column B.

It returns the relative position of the longest string in cell range B2:B.

The Index offsets that many numbers of rows and zero columns.

As per the above example, the match returns the number 10, i.e., the relative position of the longest string in B2:B.

So the Index offsets ten rows and zero columns.

Let’s try to understand the Match formula. Here it is!

Syntax:-

MATCH(search_key, range, search_type)

Search Key – max(len(B2:B))

Range – len(B2:B)

Search Type – 0

If you could understand len(B2:B) part, you can easily understand the formula. Here it is!

Note:- Within Index, we can use len(B2:B), but in standalone use, you should use it as arrayformula(len(B2:B)).

The above (column C) is the Match formula range (the LEN returns the length of characters in each name).

The search key is the MAX value in this range (column C) and which will be 9 (please see the Cyan highlighted cells).

So the Match matches the character length 9 (search key) and returns 10, which is the relative position of the search key in the range.

Option 2

Here is one more formula that you can use to find the longest string in a column in Google Sheets.

=filter(B2:B,len(B2:B)=max(len(B2:B)))

It is much cleaner than the OPTION 1 formula above.

Here, the formula filters the names based on the length of strings. It’s like this.

If the length of the strings is equal to its max length in the range, return that value.

This formula sometimes returns more than one value.

For example, if two names have nine characters each, the formula will return both.

But the Index-match will return the first name only.

Array Formula to Find the Longest String in Each Column

If you want to perform the same evaluation in each column, you can better depend on an array formula alternative to index-match.

In the below example, 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
    )
)

Formula Explanation

It’s a Vlookup array formula.

Syntax:

ARRAYFORMULA(VLOOKUP(search_key, range, index, [is_sorted]))

Here is the range reference in that formula.

Range

See the helper range H2:I5. It acts as the range in Vlookup.

Cell range I2:I5 contains all the unique strings in B2:F6.

You can manually enter that or use the below formula in I2.

=sort(filter(unique(flatten(B2:F6)),unique(flatten(B2:F6))<>""))

In H2:H5, I have entered the length of the above unique strings.

Here I have used the below formula in cell H2.

=ArrayFormula(len(I2:I5))

Now back to the array formula in cell B8 that returns the longest string in each column.

We have already seen the range used in this formula above. The search keys are the DMAX formula which is as follows.

Search Key

dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)})

Let’s test it. For that, we should wrap it with the ARRAYFORMULA function as below.

=ArrayFormula(dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)}))

The above DMAX returns the length of the largest string in each column.

Vlookup uses it as the search key to search the first column in the range H2:I5 and returns the corresponding values from I2:I5.

DMAX (Search Key) Explanation

Syntax:-

DMAX(database, field, criteria)

Database – {column(B1:F1);len(B2:F6)}

As you may know, the database functions require field labels.

Since our above range B2:F6 doesn’t contain the same, I have used column numbers as the field labels.

Let’s test the database (we should use ArrayFormula with the database in standalone test).

Field – sequence(1,columns(B1:F1),1)

It’s equal to specifying ={1,2,3,4,5}, i.e., the column indexes.

Criteria – {column(B1);if(,,)}

The first field label is 2 (please see cell B8 the image above), and =column(B1) returns that.

The IF returns a null character that means no criteria.

That’s all about how to find the longest string in each column in Google Sheets.

Thanks for the stay. Enjoy!

Similar: Find the Longest String in Each Row in Google Sheets.