Md. Abdur Rahim Rasel
Many times the database contains some special characters which we do not need in the database and we want to identify them. We can easily perform this task with the help of Excel tools and formulas. The article will explain 4 different ways that will show how to use the formula to identify special characters in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Identify Special Characters Using Formula in Excel
Let’s say, we have several Product Codes for different Products in Column C which are started with a special character #, and the name of the products are given in Column B. We will use the REPLACE, LEFT, LEN Functions, and Flash Fill commands to identify the special characters. Here’s an overview of the dataset for our today’s task.
1. Use Flash Fill Command to Identify Special Characters in Excel
The easiest way is to identify special characters in Excel by using Flash Fill Command. To identify special characters by using Flash Fill Command, follow the instructions below.
Steps:
- First, select cell D5 and manually type the special character from the Code of the Product named Eraser.
- Now, from Home Tab, go to,
Home → Editing → Fill → Flash Fill
- Finally, you will identify all the product’s special characters by pressing on the Flash Fill command.
Read More: Character Code for Check Mark in Excel (2 Applications)
2. Apply REPLACE Function to Identify Special Characters in Excel
You can use the REPLACE function to identify any character from any cell. To identify special characters in Excel by using the REPLACE function follow the steps below to learn!
Step 1:
- First of all, select an empty cell where we will type the REPLACE function.
- From our dataset, we’ll select cell D5.
- After selecting cell D5, type the following formula in the Formula Bar,
=REPLACE(C5,1,5,"#")- Here, C5 refers to the cell from which you want to identify the special character.
- 1 indicates that you want to identify the character from the first character of your text.
- 5 indicates that you want to replace five characters.
- “#” indicates that you are identifying that character.
- Hence, press Enter on your keyboard.
- As a result, you will be able to get the return of the REPLACE function and the return is #.
Step 2:
- After that, AutoFill the REPLACE function to the rest of the cells in column D which has been given in the screenshot.
Read More: How to Filter Special Characters in Excel (An Easy Guide)
Similar Readings
- How to Set Character Limit in Excel
- Check Character Limit in Excel (With Easy Steps)
- How to Insert Character Between Text in Excel (5 Easy Methods)
3. Use LEFT Function to Identify Special Characters in Excel
Using the LEFT function to identify the special character is an easy task. From our dataset, we will find the special character using the LEFT function. Let’s follow the instructions below to identify the special character!
Steps:
- First of all, select cell D5 for the convenience of our work.
- After selecting cell D5, type the following formula in the Formula Bar,
=LEFT(C5,1)- Where C5 is the text and 1 is the [num_chars] of the LEFT function.
- Hence, press Enter on your keyboard.
- As a result, you will be able to get the return of the LEFT function and the return is #.
- After that, AutoFill the LEFT function to the rest of the cells in column D which has been given in the screenshot.
Read More: How to Find Special Characters Using VBA in Excel (3 Quick Ways)
4. Combine LEFT and LEN Functions to Identify Special Characters in Excel
In this method, we’ll learn how to identify special characters in Excel By using the LEFT and the LEN functions. From our dataset, we will identify the special characters from several cells. Let’s follow the instructions below to identify the special character!
Steps:
- First, select cell D5 to identify special characters from cell C5.
- After that, type the LEFT and the LEN functions in the Formula Bar. The functions in the Formula Bar are,
=LEFT(C5,LEN(C5)-4)- Here, C5 refers to the cell from which you want to identify the character.
- The LEFT function indicates that the characters of the text will be taken from the last character.
- LEN(C5)-4 indicates that the resultant text will be the first character of the referred text (C5).
- Hence, press Enter on your keyboard.
- As a result, you will be able to get the return of the LEFT and LEN functions.
- The return is #.
- Further, AutoFill the LEFT and LEN functions to the rest of the cells in column D which has been given in the screenshot.
Things to Remember
➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.
➜ #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
Conclusion
I hope all of the suitable methods mentioned above to apply the formula to identify special characters will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
