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:
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.*"))
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".
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.
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$"))
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'")
You can use SEARCH to find a string "apple" in a cell, regardless of case:
=SEARCH("apple", A1)
This will return the position of "apple" in cell A1, regardless of whether it's "Apple", "APPLE", or "apple".
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":
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.
