Regexmatch in Filter Criteria in Google Sheets [Examples]

0
347

You can use Regexmatch in Filter Criteria in Google Sheets to apply more specific filter conditions. But you should take care to use it in Text Columns as it’s a Text function.

We can learn how to use Filter Function in Combination with Regexmatch with the help of examples. I’ve seven example formulas for you that will take you through different Regex and Filter combinations. By saying this, let’s move to our core part of this Google Sheets tutorial.

How to Use Regexmatch in Combination with Filter Function in Google Sheets

Sample Data to Use for Regex_Filter Combo.

Let’s learn how to use Regexmatch in Filter Criteria in Google Sheets by applying filter to the above data, Colum A. Here is that 7 cool Regex Filter combo examples.

We have a list of different Virgin Oils and their prices. The first column contain the Virgin Oil Name. Let’s see how to apply different conditions in this column.

Examples: Regexmatch in Filter Criteria in Google Sheets

1. Filter Column A contain all the word “Oil”, Case Sensitive.

=filter(A1:D, regexmatch(A1:A, “Oil”))

2. Filter Column A contain all the word “Olive Oil”, Case Sensitive.

=filter(A1:D, regexmatch(A1:A, “Olive Oil”)), Case Sensitive

3. Filter Column A for either Olive Oil or Coconut Oil, Case Sensitive

It’s multiple conditions in same column in Filter Function.

=filter(A1:D, regexmatch(A1:A, “Olive Oil|Coconut Oil”))

4. Filter Column A when there are any word with in bracket that only we want to filter.

=filter(A1:D, regexmatch(A1:A, “\(([A-Za-z]+)\)”))

5. Regexmatch with Filter Function, Case Insensitive.

Here it’s little tricky. You can apply any of the lower / upper / proper case with Regexmatch regular expression. Here I’ve used small case and accordingly used the regular expression in small case.

=filter(A1:D, regexmatch(lower(A1:A), “oil”))

6. How to filter a column based on 3 conditions in that same column using Regexmatch with Filter function.

=filter(A1:D,regexmatch(A1:A, “Extra Virgin Olive Oil|Pure Virgin Canola Oil|Pure Rice Bran Oil (Premium)”))

7. Filter Column A for Exact Match

Here no need to use Regex for Exact match. You can directly use the Filter function.

=filter(A1:D, A1:A=”Oil”)

That’s all for now. Use the above formulas on your own data to learn the use of it. Hope you have enjoyed the stay here.