Filter Entries if it Does Not Contain Value in Google Sheets (2 Easy Ways)

The most common and default approach to filtering is to look for a certain value in a data range. But what if we wanted to filter entries if it does not contain a value in Google Sheets?

That is easy enough to do although the methods may not seem that obvious. And of course, it can be done using both the FILTER function and the default filter of Google Sheets.

The gist of it is to take the “contains value” formula and generate something of the opposite logic.

Learn More: Google Sheets: Filter Data if it Contains Value (A Comprehensive Guide)

It is also good to know that these methods only work for text values, as any other kind can be easily filtered by using the default options.

Let’s see how it’s done.

Table of Contents

2 Ways to Filter Entries if it Does Not Contain Value in Google Sheets

1. Using the FILTER function for the “Does not Contain” Filter in Google Sheets

Using the FILTER function is the simplest approach we can show how to filter in Google Sheets and its customizability.

FILTER(range, condition1, [condition2, ...])

And as for matching text values as a condition for the FILTER function, we will use regular expressions through the use of the REGEXMATCH function.

REGEXMATCH(text, regular_expression)

Consider the following worksheet for example:

We want to filter all entries that do not contain Bricks from the data range.

Step 1: Open the FILTER function and input the data range. The data range must include the condition column, which in this case is Product.

Step 2: Input the condition. We are looking for the text “Bricks” in the data range which is in column A. So, using the REGEXMATCH function the formula will be:

REGEXMATCH(A2:A22,"Bricks")

However, this is for the direct match for Bricks. For the “does not contain” condition for this text, we must set this REGEXMATCH condition to FALSE.

REGEXMATCH(A2:A22,"Bricks")=FALSE

Step 3: Close parentheses and press ENTER.

=FILTER(A2:B22,REGEXMATCH(A2:A22,"Bricks")=FALSE)

Alternatively, you can also use a cell reference to dynamically point to the text condition for the filter. Just make sure to lock this reference with absolutes ($).

=FILTER(A2:B22,REGEXMATCH(A2:A22,$H$1)=FALSE)

For Partial Text Matches

A great part about using the FILTER and REGEXMATCH combination is that we can also filter for partial text matches, and by extension, “does not contain” filter conditions in Google Sheets.

For example, in the following worksheet, we want to filter all students that do not have the string “Jean” in them.

The formula is similar to the one we have seen before:

=FILTER(A2:B21,REGEXMATCH(A2:A21,"Jean")=FALSE)

Note that even though this formula works great with partial string filters, the match condition is still case-sensitive.

For Multiple Text Conditions

If you recall the syntax of the FILTER function, you will realize that we have multiple condition fields in the function. This means that we can filter for multiple “does not contain value” conditions using the FILTER function in Google Sheets.

For example, let’s say we want to filter all entries except Bricks and other Products that have a Not-Delivered Status:

The formula for the first condition is:

REGEXMATCH(A2:A22,"Bricks")=FALSE

The formula for the second condition is:

REGEXMATCH(B2:B22,"Not-Delivered")=FALSE

Whole FILTER formula:

=FILTER(A2:B22,REGEXMATCH(A2:A22,"Bricks")=FALSE,REGEXMATCH(B2:B22,"Not-Delivered")=FALSE)

What we’ve just done is filter for multiple criteria using the AND logic and also for multiple column conditions in Google Sheets.

The OR logical filter is also possible, and so too for values in a single column. However, unlike AND, OR logic can be only applied in a single condition field of the FILTER function and by taking advantage of regular expressions.

In this case, let us filter for entries except for Bricks and Planks. The formula will be:

=FILTER(A2:B22,REGEXMATCH(A2:A22,"Bricks|Planks")=FALSE)

Similar Readings

2. Using the Default Filter Feature of Google Sheets for the “Does not Contain” Filter

Next, we will look at how to apply the “does not contain value” filter in the default filter of Google Sheets.

Step 1: Apply a filter over the source data. Simply select a cell of the table and click on the filter icon.

Step 2: Navigate to the Filter by condition section and open the drop-down menu. Here, you’ll find the “Text does not contain” option. Select it.

Step 3: Input the string that you don’t want the table to have.

Step 4: Click OK to filter the entries that do not contain the given text in Google Sheets.

While this method does give us the results that we want, filtering in this way comes with a few limitations. The most important of which is that we cannot input multiple filter conditions for a single column for the filter in this way.

For that, we have to resort to applying custom formulas to the filter.

For Multiple Text Conditions in a Single Column

As mentioned before, we can opt to filter for multiple conditions in the default filter of Google Sheets by using a custom formula.

We will once again take advantage of the REGEXMATCH function for this.

Step 1: Navigate to the “Custom formula is” option from the Filter by condition section in the filter menu.

Filter > Filter by condition > Custom formula is

Step 2: Enter the following formula:

=REGEXMATCH(A2:A22,"Bricks|Planks")

Step 3: Click OK to apply the filter.

Final Words

That concludes the two approaches that you can use to filter entries if it does not contain a specific text value in Google Sheets. For a more customizable result for either the same or a different worksheet, we suggest the FILTER function method.

Feel free to leave any queries or advice you might have in the comments section below.