Home » Google Sheets Query: How to Use “NOT LIKE” in Query

Google Sheets Query: How to Use “NOT LIKE” in Query

by Erma Khan

You can use the following syntax in a Google Sheets query to return all rows where the value in some column is “NOT LIKE” some pattern:

=QUERY(A1:C11, "SELECT * WHERE NOT B LIKE '%uar%'")

This particular query will return all rows in the range A1:C11 where column B does not contain the pattern “uar” anywhere in the string.

Note: The % operator is a wildcard character in Google Sheets.

The following example shows how to use this syntax in practice.

Example: Use “NOT LIKE” in Google Sheets Query

Suppose we have the following dataset that contains information about various basketball players:

We can use the following query to return all rows where the value in the Position column does not contain the pattern “uar” anywhere in the string.

=QUERY(A1:C11, "SELECT * WHERE NOT B LIKE '%uar%'")

The following screenshot shows how to use this query in practice:

Google Sheets query not like

Notice that the only rows returned are the ones where the Position column does not contain the pattern “uar” anywhere in the string.

In other words, any row with a Position of “Guard” is not returned in the query.

You can also use multiple “NOT LIKE” statements in one query.

For example, we can use the following query to return rows where the Position column does not have  a pattern like “uar” and the Team column does not have a pattern like “er”:

=QUERY(A1:C11, "SELECT * WHERE NOT B LIKE '%uar%' AND NOT A LIKE '%er%'")

The following screenshot shows how to use this query in practice:

Notice that only two rows meet both of these “NOT LIKE” conditions.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

Google Sheets Query: How to Return Only Unique Rows
Google Sheets Query: How to Remove Header from Results
Google Sheets Query: How to Ignore Blank Cells in Query

Related Posts