Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to filter data by containing asterisk or other special characters in Excel?

As we know, we use asterisk mask to stand any characters when you filter data, but how can you do if you want to filter data which contains asterisk mask? Now this article introduces the method about data filtering if containing asterisk or other special characters in Excel

Filter data by asterisk or other symbol with Filter

Filter data by special symbol with Super Filtergood idea3


Count times a word(count times of a specific word appears in a single cell or a range .)

doc formula 1

Filter data by asterisk or other symbol with Filter


Suppose you have a range of data as below screen shot shown, and you want to filter out the numbers which contain asterisk in the TelePhone column, please follow step by step:
doc filter by asterisk 1

1. Select the data range you want to filter, and click Data > Filter. See screenshot:

doc filter by asterisk 2

2. Then click the Filter arrow on the column which you want to filter by to show the context menu, and click Number Filters (Text Filters) > Custom Filter. See screenshot:

doc filter by asterisk 3

3. In the Custom AutoFilter dialog, select contains from the first drop down list, and type asterisk mask ~* into the next text box. See screenshot:

doc filter 1

4. Click OK. Now you can see only the data contains * is filtered out. See screen shot:

doc filter by asterisk 5

If you want to filter data by containing other special characters, such as question mask ?, you just need to type ~? into the text box in Custom AutoFilter dialog and click OK. See screenshots:

doc filter 2

doc arrow

doc filter 3


Filter data by special symbol with Super Filter

Actually, if you have installed Kutools for Excel -- a handy Excel add-in tool, you also can use its Super Filter feature to filter data by asterisk symbol.

You can free install Kutools for Excel and try Super Filter out by following the below steps. 

Kutools for Excel, with more than 120 handy functions, makes your jobs easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Click Enterprise > Super Filter. See screenshot:

doc kte filter by asterisk 1

2. In the popping Super Filter pane, click doc select range to select a data range.. See screenshot:

Tip: You can resize and move the pane by dragging the border of the pane.

doc kte filter by asterisk 2

3. Then move mouse right to the first AND to display a underline, click on the underline to show the criteria text boxes. See screenshot:

doc kte 1doc kte filter by asterisk 3

4. Select the column you want to filter by in the first drop down list, and in the second drop down list, select Text, and select Contains in the third drop down list, then type ~* (the asterisk symbol) into the last text box. See screenshot:

doc kte 2

5. Then click Filter in the bottom of the pane, then the data with asterisk is filtered.

doc kte 3

If you want to filter other special symbol, such as ?, you need to type ~? Into the last text box next to Contains. See screenshot:

doc kte 4

The Super Filter is one of the powerful features in Kutools for Excel, with Kutools for Excel, you can merge multiple worksheet or workbooks, batch insert checkbox and optional button, split data by rows and so on. Free install Kutools for Excel to try it out which will easy your Excel working.

With Super Filter utility, you also can handle these:

Filter by multiple criteria or wildcard in Excel

Filter by substring in Excel


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    jessica · 3 years ago
    i have his account that contains 6M names. and i need help filtering their names based on the special characters.
    we have used double names for each account either contain "/" and "()". and i want help in which i can filter names with both characters. or on multiple characters. thanks in advance for replying. and trying to help. many thanks!!
  • To post as a guest, your comment is unpublished.
    jessica · 3 years ago
    i have his account that contains 6M names. and i need help filtering their names based on the special characters.
    we have used double names for each account either contain "/" and "()". and i want help in which i can filter names with both characters. or on multiple characters. thanks in advance for replying. and trying to help. many thanks!!