CONTAINS Substring Match in Google Sheets Query for Partial Match

I have dozens of tutorials related to Google Sheets Query. But I haven’t written much about the partial match, aka substring match in Google Sheets Query. So this post is dedicated to the CONTAINS Substring Match in Google Sheets Query.

Many functions in Google Sheets support Wildcards. But when it comes to Query, the Wildcard usage is different.

There are a few complex comparison operators in the Query function to achieve this.

When I say ‘complex,’ I mean comparison operators other than !=, <>, etc., albeit the usage is not complex.

Earlier, I have written a Google Sheets tutorial detailing the use of % (percentage) and _ (underscore) wildcard characters in Query using the LIKE comparison operator (please scroll down and see the “Resources”).

This time, let’s see how to use CONTAINS substring match in Google Sheets Query. I am trying to come up with some simple examples.

How to Use the Contains Substring Match in Google Sheets Query

Sample Data:

Query Contains Partial Match - Sample Data

Here column A contains different fruit names.

See how we can filter column A based on a partial-string match.

For example, I want to filter all the rows that contain “Mango” in column A.

The following Query formula will return rows 4 and 5.

=query(A1:B,"Select A where A contains 'Mango'")

Here are a few more examples of Query partial match using the CONTAINS substring comparison operator in Google Sheets.

Example to CONTAINS substring comparison operator

I think the above example is self-explanatory.

Now let me point out some peculiarities of this complex (string) comparison operator.

1. Contains in Query Where Clause for Partial Match

The Query CONTAINS substring match matches a substring in a text irrespective of its position.

It tests whether the substring is present in any part of the text, such as in the beginning, middle, or end.

Example:

=query(A1:A,"Select A where A contains 'blog'")

It will return the rows containing the text “blog” in the above sample data.

2. Case Insensitive Contains in Query Where Clause

The CONTAINS substring match in Google Sheets Query is case-sensitive. That means it will treat capital and lower-case letters differently.

But you can overcome that by using the UPPER or LOWER scalar functions.

Similar: How to Apply Bulk Change Case in Google Sheets Using Query Function

Above, the text in column A contains “magazine.” The following Query formula would return the #N/A error.

=query(A1:A,"Select A where A contains 'Magazine'"

Here is the correct formula that uses the UPPER scalar function.

=query(A1:A,"Select A where UPPER(A) contains 'MAGAZINE'")

If you prefer the SMALL scalar function, the Query partial substring match formula will be as follows.

=query(A1:A,"Select A where lower(A) contains 'magazine'")

How to Use Does Not Contain in Substring Match in Query

How to use the Query formula to return rows that do not contain a specific substring in a column?

For that, you can use the NOT logical operator together with the CONTAINS substring match.

See how I have placed the NOT logical operator within the formula.

Formula Example to the Use of NOT Logical Operator with CONTAINS:

=query(A1:A,"Select A where NOT A contains 'blog'")

That’s all.

You may have additional questions like how to put the criteria in a cell and use that with the CONTAIN.

I’m skipping that part since there is a detailed tutorial here – How to Use Cell Reference in Google Sheets Query.

You have just learned how to use CONTAINS substring match in Google Sheets Query.

Please don’t think this is the only solution for the partial match in Google Sheets Query.

You may find additional solutions under the “Resources” below. Thanks for the stay. Enjoy!

Resources

  1. Starts with and Not Starts with Prefix Match in Query.
  2. Ends with and Not Ends with Suffix Match in Query.
  3. Matches Regular Expression Match in Google Sheets Query.
  4. How to Use LIKE String Operator in Google Sheets Query.
  5. Multiple CONTAINS in WHERE Clause in Google Sheets Query.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. Sir,
    I am struggling with the following:

    Column A and C have names. The names have all kinds of variations, so an exact match is not possible.

    For example:

    Column A has:
    Sanjib Kumar & Rishav Ranjan

    Column B has:
    Sanjib Kumar
    Rishav Ranjan
    Bibhuti Bhushan

    My requirement:
    To determine if the occurrences of “Sanjib” or “Rishav” are found in both columns.

    How can I manage this?

    • I have already posted a tutorial titled “How to Perform Partial Match Between Two Columns in Google Sheets.” Please check it out for a detailed solution on how to match partial names between columns.

      Let me know if you need further assistance!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.