Formulas

How to Filter Data by Text Wildcards

Pinterest LinkedIn Tumblr

Are you aware of the text wildcards? Do you know that you can filter data by text wildcards? If not, then don’t worry, this article will help you know it all. Using text wildcards help you in filtering the data based on your conditions. Let’s look upon few examples to understand it in a better way.

How to Filter Data by Text Wildcards

Suppose, we have a list of names with us as given in the below image.

Filter data by Text Wildcards

For example, you want to show the names of the people whose names start with the letter R. Then, for that you can use the wildcard * (asterisk) which represents any number of characters. Let’s see how we can use this wildcard in filtering our data.

Step 1: Select the data.

Step 2: Go to Data Tab >> Sort & Filter Group >> Filter.

 Shortcut Key: Ctrl+Shift+L.

How to Filter Data by Text Wildcards 1

Step 3: Click on the down arrow button near your header.

How to Filter Data by Text Wildcards 2

Step 4: Once you click on the arrow button, in the search option, type R* and you will see that all names starting from the letter R will be displayed.

How to Filter Data by Text Wildcards 3

Step 5: Click on OK.

How to Filter Data by Text Wildcards 4

Similarly, if you put the asterisk sign before any letter, let’s say *h, then you will get the names that end with the letter h. You can see it in the below image.

How to Filter Data by Text Wildcards 5

Now, let us see the usage of another wildcard, that is ? (question mark). This wildcard represents single character, that means if you type, ?? then it will return you a name consisting of two letters.

So, let us take an example as, I want to see only the names of people with three letters. Then for that in the search option, we will have to write ??? as shown in the image below.

How to Filter Data by Text Wildcards 6

You can see in the down list, that we have got the searches for all those having three letter names.

 Now, let us see what does the third Wildcard ~ (tilde) means. When do I use this wildcard? We can use this wildcard when we have other wildcards present in our data and we want to find those. For example, look at the list of names given below.

How to Filter Data by Text Wildcards 7

Now, from the given list of names I want to filter the given data and get the names of people who have an asterisk sign in their name.

For, that in your search list you need to type ~* as shown in the image below.

How to Filter Data by Text Wildcards 8

Click on OK.

How to Filter Data by Text Wildcards 9

You can see that Excel has filtered the names containing a wildcard.

In this way you can easily filter data by text wildcards in Excel.

For more information visit our YouTube channel.

Related Posts

Write A Comment