How to Use Google Sheets FILTER With a Wildcard (2024 Update)

What Is a Wildcard in Google Sheets?

Before we dive into the examples, it's important to understand the two main wildcards you can use in Google Sheets:

* (asterisk): Represents any number of characters.

? (question mark): Represents any single character.

FILTER in Google Sheets Explained

The basic syntax for the FILTER function is: 

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

Where: 

range: The range of cells to filter.

condition1, condition2, ...: Conditions that the data must meet to be included in the result.

How to Use Google Sheets FILTER With a Wildcard

To use a wildcard with the FILTER function in Google Sheets, you often need to incorporate functions like SEARCH, which can search for a text string within another text string.

Example 1: Filtering Data that Contains a Specific Text

Suppose you have a list of names in column A, and you want to filter out names that contain "Jo".

=FILTER(A1:A10, ISNUMBER(SEARCH("*Jo*", A1:A10)))

This formula filters the range A1:A10 for any cells that contain "Jo" anywhere in the text. The SEARCH function looks for "Jo", and ISNUMBER checks if SEARCH returns a number (meaning "Jo" was found).

Example 2: Filtering Data that Starts with a Specific Character(s)

If you want to filter names that start with "S", you might need to use LEFT or REGEXMATCH functions, instead of SEARCH:

Here's how you can use the LEFT function:

=FILTER(A1:A10, LEFT(A1:A10, 1) = "S")

Alternatively, you can also use REGEXMATCH for a pattern:

=FILTER(A1:A10, REGEXMATCH(A1:A10, "^S"))

^S here denotes any string that starts with "S".

Example 3: Filtering Data Ending with a Specific Character(s)

To filter names that end with "a", you can use the following formula: 

=FILTER(A1:A10, REGEXMATCH(A1:A10, "a$"))

a$ denotes any string that ends with "a".

We hope that this article has helped you and given you a better understanding of how to use FILTER with a wildcard in Google Sheets. If you enjoyed this article, you might also like our articles on how to use the Google Sheets XMATCH function and how to coalesce in Google Sheets.