Have you ever wondered how to filter data in Google Sheets based on criteria that are not an exact match? Filtering where the criteria is contained within the cell can be extremely useful, especially when you’re dealing with a large dataset. In this article, I’ll show you different methods to achieve this, so you can choose the one that suits your needs best.
Filtering when the criteria is specified
Let’s start with an example where we 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 attached numbers, like “Student 1”. To filter the data in such cases, we need to use a special formula that looks for cells containing the word “student” instead of exact matches.
Use the following formula to filter the list where the cells contain the specified criteria.
=FILTER(A3:A, REGEXMATCH(A3:A, "student"))
The formula above generates a filtered list of all cells that contain the word “student”.
Filtering where the criteria is not contained
Now let’s move on to an example where we generate a filtered list where the specified criteria is not contained in the cells. In this case, we will modify the formula from the previous example.
Use the following formula to filter the list where the cells do not contain the specified criteria.
=FILTER(A3:A, REGEXMATCH(A3:A, "student")=FALSE)
The formula above generates a filtered list of all cells that do not contain the word “student”.
Filtering multiple values: logical AND (Contains both)
Sometimes, you may want to filter your list where the result must meet multiple criteria. The formula below filters cells where both specified criteria must be contained.
=FILTER(A3:A, REGEXMATCH(A3:A, "student"),REGEXMATCH(A3:A, "visitor"))
Filtering multiple values: logical OR (Contains either one)
The formula below filters data where either of the specified criteria may be contained in the cells.
=FILTER(A3:A, (REGEXMATCH(A3:A, "student"))+(REGEXMATCH(A3:A, "visitor")))
Method 2 to filter where cells contain the specified criteria
Now let’s switch to another method to achieve the same results as the previous examples, but using a different combination of formulas. This time, we will filter cells that contain partial text using the FILTER function and the SEARCH function.
Use the following formula to filter your data when the cell contains the specified text/value.
=FILTER(A3:A, SEARCH("student", A3:A))
Just like in the first example, the data will be filtered to display only the cells/entries that contain the word “student”.
Method 2 to filter where the cell does not contain the specified criteria
You can also use the FILTER and SEARCH functions to filter when the cell does not contain the specified partial text. Modify the formula from the previous example as shown below.
Use the following formula to filter data when the cells do not contain the specified criteria.
=FILTER(A3:A, ISERROR(SEARCH("student", A3:A)))
The data will be filtered to display the cells/entries that do not contain the word “student”.
Method 3 to filter where values contain the specified criteria
You can also use the QUERY function to filter based on partial text. Use the following formula, as shown in the example.
=QUERY(A3:A,"Select * where A contains 'student'", 0)
The text will be filtered to display the cells/entries that contain the word “student”.
Method 3 to filter where values do not contain the specified criteria
Use the following formula to filter data when the cells do not contain the specified criteria.
=QUERY(A3:A,"Select * where not A contains 'student'", 0)
The text will be filtered to display the cells/entries that do not contain the word “student”.
Filtering multiple criteria: logical AND (Contains both)
The formula below filters when both specified criteria/texts must be contained in the cells.
=QUERY(A3:A, "select A where A contains 'student' and A contains 'visitor'")
Filtering multiple criteria: logical OR (Contains either one)
The formula below filters data where either of the specified criteria/texts may be contained in the cells.
=QUERY(A3:A, "select A where A contains 'student' or A contains 'visitor'")
Crawlan.com offers a comprehensive Google Sheets cheat sheet that summarizes all these methods. Click here to get your hands on it and master the art of data filtering in Google Sheets!
Now that you know all the methods to filter cells that contain/do not contain criteria in Google Sheets, you can choose the one that best suits your needs. With these tricks, you can transform your spreadsheets into powerful data management and analysis tools. Maximize your productivity and achieve accurate and relevant results. Happy filtering!
