In this article:

Wildcard in Google Sheets: How to Use Them (2024 Update)

May 8, 2024

Wildcard in Google Sheets

Using a wildcard in Google Sheets can be very useful when you need to search or match partial strings. Here are a few ways you can use wildcards:

Using the * Wildcard

The * wildcard represents any sequence of characters. For example, in a FILTER function, you could use "Apple*" to match any cell that starts with "Apple" followed by any characters.

Here's a sample formula you can test:

=FILTER (A:A, REGEXMATCH(A:A, "^Ap.*"))

google sheets wildcard

Using the ? Wildcard

The ? wildcard is used to match any single character in a specific position of your text. 

For example, you have a list of file names in column A of your Google Sheet, and you want to find all file names that start with "report_" followed by any single digit and then "_2023".

wildcard google sheets

To find all file names that match your criteria, you can use the FILTER function combined with the ? wildcard. Below is a sample formula:

=FILTER(A1:A5, ISNUMBER(SEARCH("report_?_2023", A1:A5)))

This formula filters the list in A1:A5 for entries that match the pattern "report_" followed by any single character (representing a digit in our case) and then "_2023". ISNUMBER is used to ensure that SEARCH returns a numeric value, indicating a match.

wildcard in google sheets examples

In Combination with REGEX Functions

Google Sheets supports regular expressions in functions like REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE, where you can use more complex pattern matching.

Take for example, you have a list of email addresses and you want to find those that end with "@gmail.com". Your formula might be:

=FILTER(A:A, REGEXMATCH(A:A, "@gmail\.com$"))

wildcard in google sheets

Using with QUERY Function

In a QUERY function, you can use the contains, starts with, or ends with clauses for partial matches, which act like wildcards.

Suppose you have a dataset in range A:A and you want to query names that start with "J". The formula would be:

=QUERY(A:A, "select * where A starts with 'J'")

how to use a wildcard in google sheets

Case-Insensitive Matching

You can use SEARCH to find a string "apple" in a cell, regardless of case:

=SEARCH("apple", A1)

using a wildcard in google sheets

This will return the position of "apple" in cell A1, regardless of whether it's "Apple", "APPLE", or "apple".

Import Range with Wildcard

Unfortunately, you can't use wildcards in IMPORTRANGE functions directly. You would need to import the range and then use a function like FILTER or QUERY with a wildcard on the imported data.

Example: First, import a range with IMPORTRANGE and then filter it. Suppose you've imported data into range A:A and want to filter all cells containing "data":

how to use wildcards google sheets

This filters the imported range for any cells containing "data".

We hope that this article has helped you and given you a better understanding of wildcards in Google Sheets. If you enjoyed this article, you might also like our articles on how to rank in Google Sheets and how to use DSUM 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.

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