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