In this article:

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

May 8, 2024

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, ...])


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".


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).

google sheets filter wildcard

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")

filter wildcard google sheets

Alternatively, you can also use REGEXMATCH for a pattern:


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

how to use google sheets filter with wildcard

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".

google sheets filter with wildcard

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->