Intro
Master Google Sheets Query with 5 powerful techniques to filter data that does not contain specific keywords, phrases or substrings. Discover how to use the does not contain operator, regex, and more to refine your data analysis. Improve your spreadsheet skills with expert tips and examples on data filtering and manipulation.
Google Sheets is a powerful tool for data analysis, and one of its most useful features is the Query function. This function allows you to manipulate and analyze data in a variety of ways, including filtering, sorting, and aggregating. One of the most useful applications of the Query function is to exclude data that contains certain values or phrases. In this article, we'll explore five ways to use the Query function to filter out data that does not contain specific values.
Understanding the Query Function
Before we dive into the ways to use the Query function to exclude data, let's take a quick look at how the function works. The Query function uses a SQL-like syntax to select and manipulate data in a Google Sheet. The basic syntax of the Query function is as follows:
=QUERY(range, query, [headers])
rangeis the range of cells that you want to query.queryis the SQL-like query that you want to execute on the range.[headers]is an optional parameter that specifies whether the first row of the range contains headers.
Way 1: Using the "NOT CONTAINS" Operator
One of the most straightforward ways to exclude data that contains specific values is to use the "NOT CONTAINS" operator. This operator is used in the query parameter of the Query function. For example, suppose we have a range of cells that contains a list of names, and we want to exclude any names that contain the word "John". We can use the following query:
=QUERY(A1:B10, "SELECT * WHERE B NOT CONTAINS 'John'")
This query will return all rows in the range A1:B10 where the value in column B does not contain the word "John".
Way 2: Using the "NOT LIKE" Operator
Another way to exclude data that contains specific values is to use the "NOT LIKE" operator. This operator is similar to the "NOT CONTAINS" operator, but it allows you to use wildcard characters to match patterns. For example, suppose we have a range of cells that contains a list of names, and we want to exclude any names that start with the letter "J". We can use the following query:
=QUERY(A1:B10, "SELECT * WHERE B NOT LIKE 'J%'")
This query will return all rows in the range A1:B10 where the value in column B does not start with the letter "J".
Way 3: Using the "REGEXMATCH" Function
The REGEXMATCH function is a powerful tool for matching patterns in text data. We can use this function in combination with the Query function to exclude data that contains specific values. For example, suppose we have a range of cells that contains a list of names, and we want to exclude any names that contain the word "John" or "Jane". We can use the following query:
=QUERY(A1:B10, "SELECT * WHERE NOT REGEXMATCH(B, 'John|Jane')")
This query will return all rows in the range A1:B10 where the value in column B does not contain the word "John" or "Jane".
Way 4: Using the "FILTER" Function
The FILTER function is another powerful tool for filtering data in Google Sheets. We can use this function in combination with the Query function to exclude data that contains specific values. For example, suppose we have a range of cells that contains a list of names, and we want to exclude any names that contain the word "John". We can use the following query:
=FILTER(A1:B10, NOT(REGEXMATCH(B:B, "John")))
This query will return all rows in the range A1:B10 where the value in column B does not contain the word "John".
Way 5: Using the "QUERY" Function with Multiple Conditions
Finally, we can use the Query function with multiple conditions to exclude data that contains specific values. For example, suppose we have a range of cells that contains a list of names and ages, and we want to exclude any names that contain the word "John" or are older than 30. We can use the following query:
=QUERY(A1:B10, "SELECT * WHERE B NOT CONTAINS 'John' AND C < 30")
This query will return all rows in the range A1:B10 where the value in column B does not contain the word "John" and the value in column C is less than 30.
Google Sheets Query Does Not Contain Image Gallery
We hope this article has helped you learn how to use the Query function to exclude data that contains specific values in Google Sheets. Whether you're using the "NOT CONTAINS" operator, the "NOT LIKE" operator, the REGEXMATCH function, the FILTER function, or the Query function with multiple conditions, we're confident that you'll be able to manipulate and analyze your data with ease. If you have any questions or need further assistance, please don't hesitate to ask.