How to Filter Special Characters in Excel – 3 Methods

What Is a Special Character?

Non-alphabetic and non-numeric characters are called the special characters. The punctuation marks and other symbols, like ~,&,?,>,*,&,$,@ are examples of special characters.

Using the Custom Autofilter to Filter Special Characters in Excel

The dataset contains Names and Phone numbers. There are special characters within the numbers.

Use the Custom Autofilter to filter special characters.

Method 1 – Applying a Filter for a Single Special Character

Steps:

• Select any cell in the dataset.
• Click Home > Sort & Filter > Filter.

The triangular filter icon available in every header of the dataset.

• Click the filter icon in the Phone Number column.
• Click Text Filters > Custom Filter.

It shows the whole phone numbers; there are no options with special characters to filter by default.

• Select contains.

• You can also click  Text Filters > Contains to open the Custom Autofilter dialog box, and select contains.

• Enter the Tilde character (~) before the special character you want to search. Here, Question mark (?).
• Click OK.

All cells with ‘?’ are displayed.

Method 2 – Filtering Two Special Characters in Excel

Steps:

• Follow the first four steps in the first section to open the Custom Autofilter dialog box.
• Select contains.
• Enter the Tilde character (~) before the special characters. Here, Question mark (?) and Asterisk (*).
• Check And.
• Click OK.

The cell that contains both special characters is displayed.

Method 3 – Applying a Filter for Either of the Two Special Characters

Steps:

• Follow the first four steps in the first section to open the Custom Autofilter dialog box.
• Select contains and enter the special characters. Here, The question mark (?) and Hyphen (-).
• Check Or.
• Click OK.

Cells that have the Question mark (?) or Hyphen (-) will be displayed.

Download Practice Workbook

Download the free Excel workbook.

<< Go Back to Learn Excel