Many people know how to filter in Google Sheets where the criteria is an exact match, but there will be times when you want to filter where the criteria is contained within the cell, even if it is not an exact match to the cell's value. This can be done in several ways, and in this article I am going to show you all of them so you can pick the method that works best for you.
I am going to teach you how to filter where the criteria is contained, where the criteria is not contained, and I'll show you how to use multiple criteria in the filter. You may have searched for a FILTER wildcard feature, like with some functions where you can include an asterisk to indicate a wildcard… but for the FILTER function the asterisk does not work as a wildcard, and the formulas in this lesson are the solution you are looking for.
There are multiple formulas / formula combinations that can filter where the cell contains partial text, and so below are examples of the different formulas, so that you can pick the option that you like the most. For each example, the unfiltered data is in column A, the filter formula is in cell C3, and the filtered list is in column C.
Filter where contains specified criteria
In this example, we are going to filter a list of text, and display only the cells that contain the word "student".
As you can see in the example image, there are cells in the list that contain the word "Student" but also have numbers attached to them, like this "Student 1". So we need to use a special formula to filter the data where the cells contain the word "Student", instead of looking for an exact match like we normally do when filtering.
To do this we will use the REGEXMATCH function along with the FILTER function. The REGEXMATCH formula helps us to specify the criteria for the filter function, to specify partial text as the criteria instead of exact text.
Use the formula below to filter the list where the cells contain the specified criteria.
=FILTER(A3:A, REGEXMATCH(A3:A, "Student"))
As you can see in the image above, the formula above generates a filtered list of all the cells that contain the word "Student".
Filter where does NOT contain specified criteria
Now let's go over an example where we generate a filtered list, where the specified criteria is not contained in the cells. In this case we are going to filter the list, and display only entries that do not contain the word "Student". To do this we will modify the formula from the previous example, as shown below.
Use the formula below to filter the list where the cells do not contain the specified criteria.
=FILTER(A3:A, REGEXMATCH(A3:A, "Student")=FALSE)
As you can see in the image above, the formula above generates a filtered list of all the cells that do NOT contain the word "Student".
Filter contains multiple values: AND logic (Contains both)
Sometimes you may want to filter your list, where the result must contain multiple criteria. The formula below will filter where both specified criteria must be contained in the cells.
=FILTER(A3:A, REGEXMATCH(A3:A, "Student"),REGEXMATCH(A3:A, "Visitor"))
Filter contains multiple values: OR logic (Contains either)
The formula below will filter data, where either specified criteria can be contained in the cells.
=FILTER(A3:A, (REGEXMATCH(A3:A, "Student"))+(REGEXMATCH(A3:A, "Visitor")))
Method 2 to filter where the cells contain specified criteria
Let's go over another way to do the same thing as the previous examples, but with using a different formula combination. This time we will filter where the cells contain partial text by using the FILTER function and the SEARCH function.
Use the formula below to filter your data where the cell contains the specified text / value.
=FILTER(A3:A, SEARCH("Student", A3:A))
As you can see in the image above, just like in the first example, the data has been filtered to display only the cells / entries that contain the word "Student".
Method 2 to filter where the cell does NOT contain specified criteria
You can also use the FILTER and SEARCH function to filter where the cell does not contain the specified partial text. To do this, modify the formula from the previous example, as in the formula below.
Use the formula below to filter the data where the cells do not contain the specified criteria.
=FILTER(A3:A, ISERROR(SEARCH("Student", A3:A)))
As you can see in the image above, the data has been filtered to display the cells / entries that do not contain the word "Student".
Method 3 to filter where the values contain specified criteria
You can also use the QUERY function to filter by partial text. To do this, use the formula below, as shown in the example.
=QUERY(A3:A,"Select * where A contains 'Student'", 0)
As you can see in the image above, the text has been filtered to display the cells / entries that contain the word "Student".
Method 3 to filter where the values do NOT contain specified criteria
Use the formula below to filter the data where the cells do not contain the specified criteria.
=QUERY(A3:A,"Select * where not A contains 'Student'", 0)
As you can see in the image above, the text has been filtered to display the cells / entries that do not contain the word "Student".
Filter contains multiple criteria: AND logic (Contains both)
The formula below will filter where both of the specified criteria / partial text must be contained in the cells.
=QUERY(A3:A, "select A where A contains 'Student' and A contains 'Visitor'")
Filter contains multiple criteria: OR logic (Contains either)
The formula below will filter data, where either specified criteria / partial text can be contained in the cells.
=QUERY(A3:A, "select A where A contains 'Student' or A contains 'Visitor'")
