Table of Contents
Google Sheets Query is a powerful tool that allows users to extract data from a spreadsheet based on specific conditions. One of the most commonly used operators in a query is “LIKE”, which helps to filter data based on a partial match. However, in cases where the user wants to exclude certain data from the results, the “NOT LIKE” operator can be used. This operator allows users to specify a pattern that should not be present in the data being queried. By using “NOT LIKE” in a Google Sheets Query, users can effectively filter out unwanted data and obtain a more refined and accurate set of results.
Google Sheets Query: Use “NOT LIKE” in Query
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:
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%'")
Notice that only two rows meet both of these “NOT LIKE” conditions.
Cite this article
stats writer (2024). How can I use “NOT LIKE” in a Google Sheets Query?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-not-like-in-a-google-sheets-query/
stats writer. "How can I use “NOT LIKE” in a Google Sheets Query?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-not-like-in-a-google-sheets-query/.
stats writer. "How can I use “NOT LIKE” in a Google Sheets Query?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-not-like-in-a-google-sheets-query/.
stats writer (2024) 'How can I use “NOT LIKE” in a Google Sheets Query?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-not-like-in-a-google-sheets-query/.
[1] stats writer, "How can I use “NOT LIKE” in a Google Sheets Query?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use “NOT LIKE” in a Google Sheets Query?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.