Afrina
Sometimes while working on a dataset, you need some specific data or characters. Finding or sorting those particular data in the dataset manually is very tiring and time-consuming at the same time. This article covers the solution to this problem. In this article, we will learn how to filter data with wildcard in Google Sheets.
A Sample of Practice Spreadsheet
You may copy the spreadsheet below and practice yourself.
What Are Wildcard Characters in Google Sheets?
Wildcards are special characters that can represent other characters very specifically. We usually use the following wildcard characters in Google Sheets.
- Asterisks(*): This character represents all characters that remain before or after the specified position.
- Question Mark(?): It represents a single character in the specified location.
- Tilde(~): This wildcard character instructs that the following character should be used as a regular character rather than a wildcard.
3 Suitable Examples of Using Filter with Wildcard in Google Sheets
The dataset below contains Writer’s Name, Book Name, and Rating. Suppose you need to filter the dataset for the book ratings of specific writers. Here, we will demonstrate how to use the filter tool with wildcard to do that in Google Sheets. Let’s start.
1. Filter Data with Partial Match
Here we will filter the dataset for partial matches in the first, last, and middle names of the writers in the dataset.
1.1 First Name
Suppose, you need to collect the rating of all the books whose writers’ name starts with Andrew. You can use the Filter tool with the Asterisk(*) wildcard after Andrew as Andrew* to get the required result. The steps are mentioned below.
📌 Steps:
- First, select the header row from the dataset, the range is B4:D4.
- Then, select Data from the menu bar and Create a filter from the drop-down list.
- After that, the filter icons will be visible in the header row.
- Next, click the filter icon in cell B4, and a drop-down list will pop up.
- Next, select Filter by condition >> Text contains from the drop-down list as below.
- After that, enter Andrew* into the Value or formula box and click OK.
- As a result, the output will be as below.
Read More: How to Use Wildcard in Google Sheets (3 Practical Examples)
1.2 Last Name
Here we will collect the ratings of all the books whose writer’s name ends with Gill. You can filter the dataset using the Asterisk(*) wildcard before Gill as *Gill to do that.
📌 Steps:
- First, select anywhere in the dataset and go to Data >> Create a filter as earlier. Then select Text contains from Filter by condition.
- Next enter *Gill into the Value or formula box to get every value that contains Gill at the end, and click OK.
- Lastly, the output will be as below.
1.3 Middle Name
Now assume you want to collect the rating of all the books whose writer’s middle name is Stuart. You can use the Asterisk(*) wildcard before and after Stuart as *Stuart* to filter them.
📌 Steps:
- First, apply Filter to the dataset, click on the filter icon in the Writer’s Name cell and then select Text contains from Filter by condition by following the earlier steps.
- Next enter *Stuart* into the Value or formula box, to get every value that contains Stuart in the middle and click OK.
- After that, you will get the following result.
2. Extract Data with Specific Number of Characters
Suppose, you need to collect the rating of all the books whose writer’s name starts with A, ends with w, and contains exactly 4 characters in between. You can use the Question Mark(?) wildcard to filter the dataset for that.
📌 Steps:
- First, select Text contains from Filter by condition by following the earlier steps.
- Then enter A????w into the Value or formula box to get the value that contains A in the first place, w in the last place and there will be four more letters in the middle. Next click OK to filter the dataset based on these criteria.
- Finally, the filtered dataset will look as follows.
3. Data Containing Wildcard Characters
Now suppose some book names contain an asterisk (*) character which is a wildcard character itself. So how to filter those books from the dataset? Well, you can use the Tilde(~) character before the wildcard character to do that while filtering the dataset. Then the filter tool will consider the asterisk in the dataset as a simple character.
📌 Steps:
- First, click on the filter icon in the Book Name cell and select Text contains from Filter by condition by following the earlier steps.
- Then enter Direction~* into the Value or formula box to get the value that contains wildcards at the end as a random value and click OK as before.
- Consequently, you will see the desired result as follows.
Things to Remember
- The FILTER function doesn’t support wildcard characters. So you can use the Filter tool instead to filter data easily with wildcard criteria.
- You can save the filtered output so that you don’t have to filter the dataset repeatedly.
Conclusion
In this article, we explained how to filter data with wildcard in Google Sheets with some practical examples. Hopefully, this article will help you to solve problems regarding this issue. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.
