How to Filter Special Characters in Excel (An Easy Guide)

Get FREE Advanced Excel Exercises with Solutions!

While filtering in Excel, no direct command can filter the special characters. In this particular situation, we had to apply a custom filter in different ways. In this article, we’ll cover those easy ways to filter special characters in Excel with sharp steps and clear illustrations.


What Is Special Character?

The non-alphabetic and non-numeric characters are called the special characters. the punctuation marks and other symbols on our keyboard like ~,&,?,>,*,&,$,@ are examples of special characters. Special characters are widely used in programming languages.


Using Custom Autofilter to Filter Special Characters in Excel

First of all, get introduced to our dataset which represents some person’s phone numbers. Take a look; there are some special characters within the numbers. Now, we’ll use the Custom Autofilter command to filter for specific special characters.


1. Applying Filter for Single Special Character

In our very first section, we’ll apply the Custom Autofilter of Excel to filter out only one kind of special character from the data.

Steps:

  • First, select any cell from the dataset.
  • Click as follows: Home > Sort & Filter > Filter.

Using Custom Autofilter to Filter Special Characters in Excel

Now, see the triangular filter icon available in every header of the dataset.

  • Next, click on the filter icon of the Phone Number column.
  • Then, from the drop-down menu, click as follows: Text Filters > Custom Filter.

From the image below, you also see that it is only showing the whole phone numbers; there are no options with individual special characters to filter by default.

Using Custom Autofilter to Filter Special Characters in Excel

  • After that, select contains from the top-left dropdown box.

Using Custom Autofilter to Filter Special Characters in Excel

  • Or, you can directly click as follows: Text Filters > Contains to open the Custom Autofilter dialog box with contains command at once.

Using Custom Autofilter to Filter Special Characters in Excel

  • Later, always insert the Tilde character (~) before the special character that you want to search for in the next text box. I filtered out for Question mark character (?).
  • Finally, just press OK.

Now have a look, it’s now showing all the cells with the ‘?’ character.


2. Filtering for Two Special Characters in Excel

You can search for two characters at once, which means it will filter those cells that have both two characters. We’ll just use the And operator in the Custom Autofilter dialog box.

Steps:

  • First, follow the first four steps from the first section to open the Custom Autofilter dialog box.
  • After that, select the contains command in two drop-down boxes on the left side.
  • Then insert the two special characters in the two text boxes on the right side. Don’t forget to insert the Tilde character (~) before the special characters. Here, I filtered for Question mark character (?) and Asterisk (*).
  • Finally, mark the And operator and press OK.

Now, see, it’s showing the cell that contains both the special characters.


3. Applying Filter for Either of Two Special Characters

The Custom Autofilter also has an Or operator; by using that we can filter for the cells that have at least one of two special characters.

Steps:

  • Again, follow the first four steps from the first section to open the Custom Autofilter dialog box.
  • Later, again select the contains command from the left two drop-down boxes and insert the special characters in the two right text boxes. Here, I filtered for the special characters- The question mark (?) and Hyphen (-).
  • Lastly, just mark the Or operator and press OK.

Using Custom Autofilter to Filter Special Characters in Excel

Soon, you will get the cells that have the Question mark (?) or Hyphen (-) character.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to filter special characters in Excel. Feel free to ask any question in the comment section and please give me feedback.


<< Go Back to Characters | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo