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

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.


Download Practice Workbook

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


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 first 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, 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 which 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.

Read More: Apply Formula to Identify Special Characters in Excel (4 Methods)


2. Filtering for Two Special Characters in Excel

You can search for two characters at once, which means it will filter those cells which have both two characters. For 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.

Read More: How to Find Special Characters in Excel (3 Easy Methods)


Similar Readings


3. Applying Filter for Either of Two Special Characters

The Custom Autofilter has also an Or operator by using that we can filter for the cells which 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 right two 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.

Read More: How to Check If Cell Contains Special Character in Excel (2 Ways)


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. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo