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.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

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...

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.