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

Google Sheets has improved in functionality by quite a bit over the years. We now have not only one but two different ways we can filter data if it contains a value in Google Sheets.

First is the traditional filter, which now contains a plethora of different default options that seemingly covers any condition that the user might throw at it.

The second is the FILTER function, which works similarly to the regular filter but in the function form. Meaning that its potential for filter customizations is virtually endless!

Let’s see how we can utilize each option to work with the topic of the day.

How to Filter Data if it Contains Value in Google Sheets

For this section, we will use the traditional filter of Google Sheets. For the following examples, we have created a dataset and applied a filter to it:

applied filter for filter contains in google sheets

1. Filter Data if it Contains Text in Google Sheets (Includes Partial Text Match)

As we have mentioned before, the most used filter conditions can be found in the traditional filter itself. More precisely, in the Filter by condition menu:

Filter > Filter by condition > Drop-down menu

filter by condition menu

As you can see right at the top, there are already several text-related filter conditions. We can utilize these options to filter data if it contains specific text values in Google Sheets.

For this example, we will filter all entries that contain the text “Jean” in our dataset.

Step 1: Select the “Text contains” option from the Filter by condition section of the filter. “Text contains” searches for the given text within the cells and returns all matching instances.

selecting the text contains option in filter menu in google sheets

Step 2: Enter the desired text. In our case, it is ‘Jean’.

inputting the text contains value to filter

Step 3: Click OK to apply.

filter in text contains jean in google sheets using traditional filter

Alternative: Using a Custom Formula

Instead of putting the name down in the filter every time, we can dynamically reference a cell that contains the name to filter the data in Google Sheets.

For that, we have to use a custom formula in the filter. And to match text, we must use the REGEXMATCH function to incorporate regular expressions.

Step 1: To apply a custom formula, scroll down the Filter by condition menu to find the “Custom formula is” option.

the custom formula is option in the filter menu

Step 2: Enter the following custom formula:


inputting the custom formula to filter in google sheets

Note: We have used absolute cell reference ($) for the search value (cell F1) to lock the cell reference for the data range.

Step 3: Click OK to apply.

using custom formula to find if filter contains value in google sheets

Learn More: How to Filter Custom Formula in Google Sheets (3 Easy Examples)

As you can see, both methods include partial text matches as well.

On top of that, these approaches are also case insensitive. Depending on the scenario, this can be considered an advantage or a disadvantage.

However, if you want a case-sensitive filter, there is always the Text is exactly” option.

alternative text is exactly option

2. Filter if Data does not Contain Value in Google Sheets

Filtering data that does not contain a value in Google Sheets follows similar principles as filtering data that do.

Let’s say we want to find all data entries that do not contain a text or a partial text, which in this case is “Jean”.

We can once again find the perfect option right in the filter menu under the Filter by condition section. This is the “Text does not contain” option.

Filter > Filter by condition > Text does not contain

the text does not contain option in the filter by condition menu

Enter the text “Jean” and press OK to filter the dataset to show all entries that do not contain the text.

filter entries that does not contain text in google sheets

Alternative: Using a Custom formula to Filter Data that does not Contain Text

Like before, we can also implement a custom formula to filter entries that do not contain a value in Google Sheets.

And like before, we must enter the formula under the “Custom formula is” option in the filter.

Filter > Filter by condition > Custom formula is

Of course, we have to modify the formula to satisfy the requirements. The new formula is:


custom formula to filter entries that does not contain text in google sheets

The FALSE condition added to the end of the formula simply gives us the opposite result to “contains text”.

3. Filter Contains Multiple Criteria in Google Sheets (Text Starts With)

Oftentimes we may be asked to filter with multiple conditions.

For example, let’s say that on top of finding all entries with “Jean” we also want to find all entries that start with the letter “A”.

Both conditions are valid and are possible to achieve individually.

Unfortunately, the traditional filter does not have an option to work with multiple criteria. Yet.

But worry not! Where the default options fail, the custom formula succeeds.

Thanks to formulas being so flexible in Google Sheets, we can customize our existing REGEXMATCH formula to take in multiple conditions.

Let’s see the process step-by-step:

Step 1: Here we have the original formula that we used to find and filter the text “Jean” previously. This shows how Google Sheets views our formula and how it is used in the filter.


first match condition with regexmatch function

Step 2: We use the same principles to create a regular expression formula to satisfy the condition “text starts with A”. The regular expression of the symbol that determines the starting point of a text is “^”.

Thus, the formula is:


second match condition with regexmatch function

Step 3: Combine the two formulas following the “Or” condition. Thankfully, we have the OR function ready and available.


combining to form the final custom formula to filter if it contains value

This is the custom formula we will use for the filter.

Step 4: Input this formula in the custom formula field of the filter. Click OK to apply.

entering the custom formula in the filter

The result:

filter entries if it contains jean and starts with a in google sheets

Using the FILTER Function to Filter Values if it Contains Specific Value in Google Sheets

The FILTER function is a great way to approach filtering in Google Sheets. This is because, on top of preserving the source data, it has all the advantages of a function’s features in a spreadsheet.

FILTER function syntax:

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

filter function syntax

Using the FILTER function is much like working with custom formulas for filters, only this time, the results are shown separately.

Let’s see how we can use this function to filter data that contains text in Google Sheets.

1. Filter Data Containing a Single Text

Step 1: Open a FILTER function in any cell of the worksheet. An advantage of using formulas is that we can present the results in the same or different worksheets.

opening the filter function in the same worksheet

Step 2: Select the data range for the function.

selecting the range for the filter function

Step 3: Input the filter criteria. Like in custom formulas, we will once again use REGEXMATCH to find the text criteria from the data source. We can simply put in the same formula:

entering the filter criteria to filter if it contains text in google sheets

Step 4: Close parentheses and press ENTER.


filter entries if it contains given text in google sheets using the filter function

2. Filter Data Containing Multiple Texts

It is when we have multiple criteria, we need to rethink our original approach.

The multiple conditions that the FILTER function allows us is in the AND logic. So, putting different regular expression matches as conditions will not work for the OR criteria.

  • Has text “Jean” OR starts with “A”: Will not work
  • Has text “Jean” AND starts with “A”: Will work

the different conditions of the filter function is in the and logic

So, we must take a different approach. Luckily, regular expressions are quite versatile. We can use the OR argument for regular expressions within the first REGEXMATCH function.

The symbol for regular expression OR condition is “|”.

So, if we want to filter data that either contains the text “Jean” or if the value starts with “A”, the filter formula will be:


using filter function for multiple criteria of contains in google sheets

Alternatively, you can also use the plus (+) symbol to implement the OR condition:


or logic with plus symbol alternative to the previous filter formula

Final Words

That concludes our simple guide on how to filter data if it contains a value in Google Sheets.

Using either traditional filters or FILTER formulas has its own advantages and disadvantages. But in both cases, custom formulas give more freedom to the users to exactly filter as required.

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

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply