@mhasnain2000
You may use Advance Filter
For such sample
keep B1 empty and in B2 formula
=[SUMPRODUCT[--[UNICODE[MID[A2,ROW[INDIRECT["1:"&LEN[A2]]],1]]>=132]]>0]
which checks if in text are any non-ascii characters - UNICODE[]>=132
After that select your entire range in column A, Data->Advanced Filter and here
You may filter in place, but better to copy filtered data into new column.
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.
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.
- After that, select contains from the top-left dropdown box.
- Or, you can directly click as follows: Text Filters > Contains to open the Custom Autofilter dialog box with contains command at once.
- 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
- How to Set Character Limit in Excel
- Check Character Limit in Excel [With Easy Steps]
- Character Code for Check Mark in Excel [2 Applications]
- How to Insert Character Between Text in Excel [5 Easy Methods]
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.
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
- Add a Character in Excel to Multiple Cells [5 Easy Ways]
- How to Convert Special Characters in Excel [6 Easy Ways]
- Add Characters in Excel Formula [4 Simple Methods]
- How to Find Special Characters Using VBA in Excel [3 Quick Ways]