Google Sheets: How to Use FILTER with Wildcard


You can use the following formula in Google Sheets to use a wildcard character within a FILTER function:

=FILTER(A2:C11,search("avs",A2:A11))

This particular formula will filter the cells in the range A2:C11 to only contain the rows where the value in A2:A11 contains “avs” somewhere in the cell.

The following example shows how to use this formula in practice.

Example: Use FILTER with Wildcard in Google Sheets

Suppose we have the following dataset in Google Sheets that contains information about various basketball players:

Now suppose that we would like to filter the dataset to only contain rows where the value in the Team column contains “avs” somewhere in the name.

We can type the following formula into cell A13 to apply this filter:

=FILTER(A2:C11,search("avs",A2:A11))

The following screenshot shows how to use this formula in practice:

Google Sheets filter with wildcard

Notice that the only rows displayed in the filtered dataset contain the string “avs” somewhere in the team name.

To search for a different string, simply replace “avs” with another string in the formula.

For example, we can use the following formula to filter for rows where the team name contains “ets” somewhere in the name:

=FILTER(A2:C11,search("ets",A2:A11))

The following screenshot shows how to use this formula in practice:

Now the only rows displayed in the filtered dataset contain the string “ets” somewhere in the team name.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Use Wildcard Characters in Google Sheets Query
How to Use COUNTIF with Wildcards in Google Sheets

2 Replies to “Google Sheets: How to Use FILTER with Wildcard”

  1. Is there a way to use 2 wildcard filters? If the search column has multiple words, can I filter for 2 different strings to exclude data with a specific word? I tried to just put it in the =Filter function, but I get a parse error because I don’t know how to say “not equal” in the formula.

    1. Hi Cynthia…In Google Sheets, you can use the `FILTER` function along with multiple conditions to include or exclude data based on specific criteria. To use two wildcard filters and exclude data containing specific words, you can combine the `FILTER`, `REGEXMATCH`, and `NOT` functions.

      Here’s how you can do it:

      ### Excluding Rows with Specific Words
      Suppose you have a dataset in column A, and you want to exclude rows that contain either “word1” or “word2”. You can use the following formula:

      “`plaintext
      =FILTER(A:A, NOT(REGEXMATCH(A:A, “word1|word2”)))
      “`

      This formula works as follows:
      – `REGEXMATCH(A:A, “word1|word2”)` checks if the cells in column A contain “word1” or “word2”.
      – `NOT(REGEXMATCH(A:A, “word1|word2”))` returns `TRUE` for cells that do not contain “word1” or “word2”.
      – `FILTER(A:A, …)` filters the column A based on the condition provided.

      ### Example
      Let’s assume your data is in column A from A1 to A10. You want to filter out rows that contain “apple” or “banana”. The formula would be:

      “`plaintext
      =FILTER(A1:A10, NOT(REGEXMATCH(A1:A10, “apple|banana”)))
      “`

      ### Applying Multiple Filters
      If you need to apply more complex conditions, you can use logical operators within the `FILTER` function. For instance, if you want to include rows that do not contain “apple” and do not contain “banana” while also satisfying some other condition, you can extend the formula.

      For example, to include rows that do not contain “apple” and “banana” and also have a value greater than 10 in column B, use:

      “`plaintext
      =FILTER(A1:A10, NOT(REGEXMATCH(A1:A10, “apple|banana”)), B1:B10 > 10)
      “`

      ### Summary
      – Use `REGEXMATCH` for wildcard filtering with the `|` symbol to combine multiple conditions.
      – Use `NOT` to exclude rows containing specific words.
      – Combine multiple conditions in the `FILTER` function using logical operators.

      This approach should help you filter your data effectively using multiple wildcard conditions in Google Sheets.

Leave a Reply

Your email address will not be published. Required fields are marked *