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

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.

formula to identify special characters in excel


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.

Use Flash Fill Command to Identify Special Characters in Excel

  • 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.

Use Flash Fill Command to Identify Special Characters in Excel

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.

Apply REPLACE Function to Identify Special Characters in Excel

  • 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 #.

Apply REPLACE Function to Identify Special Characters in Excel

Step 2:

  • After that, AutoFill the REPLACE function to the rest of the cells in column D which has been given in the screenshot.

Apply REPLACE Function to Identify Special Characters in Excel

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


Similar Readings


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.

Use LEFT Function to Identify Special Characters in Excel

  • 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.

Use LEFT Function to Identify Special Characters in Excel

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).

Combine LEFT and LEN Functions to Identify Special Characters in Excel

  • 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.

Combine LEFT and LEN Functions to Identify Special Characters in Excel


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo