How to Remove Special Characters in Excel?

Many times the database contains some special characters that we do not need in the database, and we want to remove 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 remove special characters in Excel.

The following image shows an overview of the article, which represents the removal of special characters in Excel.

How to Remove Special Characters in Excel: 5 Easy Methods

In the following, I have described 5 simple ways to remove special characters in Excel. Follow the instructions below.

Suppose we have a dataset containing some Employee ID and Name. But the name has some special characters inside it. Now we will remove those special characters using some simple tricks. Stay tuned!

1. Using Excel Functions to Remove Special Characters

Excel has useful formulas which you can use to remove special characters in Excel. They are formed using the functions like SUBSTITUTE, RIGHT, and LEFT. We will look into each of them one by one.

1.1 Applying SUBSTITUTE Function

Let us start with the SUBSTITUTE function. It is used to replace a character with another.

Steps:

• First, choose a cell (E5) and write the below formula down-

• Gently hit ENTER and drag the “Fill Handle” down to fill.
• Finally, you will get the special characters removed from the cells. Simple, isn’t it?

1.2 Combining RIGHT, and LEN Functions to Delete Special Characters

If you want to remove some characters from the right side, then you can combine the RIGHT and LEN functions to delete characters from the right.

Steps:

• Simply, choose a cell (E5) and apply the below formula down-

Where,

• The LEN function provides the length of the texts.
• The RIGHT function returns a specific number of text counting from the right.

• Then hit the ENTER key and drag down the “Fill Handle”.
• In conclusion, you will get all the special characters from the right removed.

1.3 Utilizing LEFT, LEN Functions

Just like the previous sub-method, you can remove a special or any character from the left side with the help of LEFT and LEN functions.

Steps:

• Similarly, choose a cell (E5) and write the below formula down-

Where,

• The LEN function extracts the length of texts from the string.
• The LEFT function then removes 2 characters from the left and provides an output of #Sen.

• Hence, press ENTER key from the keyboard and pull the “Fill Handle” down to fill all the columns.
• In conclusion, you will get all the special characters removed from the left within a moment.

2. Implementing Flash Fill Feature to Remove Special Characters

Moving on with Excel tools. The Flash Fill is the easiest way to remove special characters in Excel.

Suppose we have a dataset with some special characters just like the following image. Let’s remove them using the Flash Fill feature.

Steps:

• Now, select a cell (D5) and type “Sen” manually from the keyboard.

• Then choose another cell (D6) and hit the “Flash Fill” option from the Home Ribbon.

• In summary, you will see the Excel flash fill feature has automatically filled the other cells in the column removing special characters.

3. Applying Find & Replace Feature

In some situations, the Find and Replace command can be your savior to erase special characters.

Suppose we have a dataset with some special characters in the Name column. Let’s erase those special characters and collect only the names in a new column.

Step 1:

• First, choose cells (C5:C11) and press CTRL+C to copy.

• Hence, select cells (D5:D11) and hit CTRL+V to paste.

Step 2:

• Hence, selecting the pasted output, use the keyboard shortcut CTRL+F to open the Find and Replace window.

• In the “Find what” box, type special characters “$&%” and leave the “Replace with” box blank.
• Finish the process by clicking “Replace All”.

• Within seconds, a confirmation window will pop up confirming all the replacements.

• In summary, we have successfully extracted the names, removing all the special characters.

4. Using Power Query Tool to Eliminate Special Characters in Excel

Certainly, if you are using Microsoft Excel 2016 or Excel 365 then you can use Power Query to remove special characters in Excel. In case, you are using Microsoft Excel 2010 or 2013, you can install it from the Microsoft website. You can follow the steps to use Power Query to remove special characters from your dataset.

Steps:

• First, select your range of data along with the header.
• Then choose From Table/Range from the Data

• Within a moment, you will find a small box. Check the range of your selected data and tick My table has headers
• After that, click OK.

• Thus, A new window named Power Query Window will open.
• Thereafter, select Custom Column from the Add Column tab in the Power Query.

• Therefore, it will open the Custom Column Write “Output” in the New column name option. You can write any name you want.
• Then, write the formula below in the Custom column formula option-

• Afterward, hit the OK button.

• Thereafter, click Close & Load from the File tab of the window.

• Finally, you will find a new worksheet in your workbook where you will see the final result as shown here.

5. Applying VBA Code to Remove Special Characters in Excel

You can also apply VBA code to remove special characters from cells. In this VBA code, first, we will define a user-defined function and then apply it to remove special characters. Follow the instructions to learn.

Steps:

• First, open the worksheet and press ALT+F11 to open the “Microsoft Visual Basic for applications” window.

• Then inside the new module place the following code and click “Save”-

Function Erase_Special_Characters(Txt As String) As String
Dim xx As String
Dim yy As Long
xx = "$&%"
For yy = 1 To Len(xx)
Txt = Replace$(Txt, Mid$(xx, yy, 1), "")
Next
Erase_Special_Characters = Txt
End Function

• Now, coming back to the worksheet, choose a cell (D5) and write the below formula down-

• Simply, hit the ENTER key and drag down the “Fill Handle” to fill.
• Finally, you will get all the special characters removed from the cells.

Read More: How to Remove Characters from String Using VBA in Excel

Things to Remember

• While using the Flash Fill feature to fill cells, the columns or rows have to be adjacent to each other. Otherwise, it won’t work.
• Unfortunately, if you are using Microsoft Excel versions older than 2010, you might not be able to install Power Query. You can use this feature only with versions 2010 to the latest.
• Anyway, all the methods have pros and cons, so use them wisely according to your requirements.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, I have tried to cover almost all the methods on how to remove special characters in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.

Related Articles

• How to Remove Characters from Left in Excel
• How to Remove Characters from Left and Right in Excel
• How to Remove Numeric Characters from Cells in Excel
• How to Remove Non-numeric Characters from Cells in Excel
• How to Remove Characters After a Specific Character in Excel
• Excel Remove Characters From Right
• How to Remove Specific Characters in Excel