Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Remove Special Characters in Excel (5 Easy Methods)

IMany times the database contains some special characters which 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.

Overview of removing special characters in excel using excel functions, features and VBA code

			

Download Practice Workbook

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


5 Ways to Remove Special Characters in Excel

In the following, I have described 5 simple ways to remove specific 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!

Sample dataset containing id number and names with special character


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-
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,"#",""),"!",""),"$",""),"%",""),"&","")
Formula Description:

The syntax of the formula:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

  • text=the text which you want to work with.
  • old_text= text which you want to remove.
  • new_text= replaced text. ( For our case we replace it with blank “”).
  • instance_name= the number of the special character in case of recursive characters present in the text.

The formula of SUBSTITUTE to remove special characters in Excel

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

Final Output after removing special characters


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-
=RIGHT(C5,LEN(C5)-1)

Where,

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

Formula of RIGHT, LEN functions to delete special characters

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

Final result after deleting special characters from the right hand side


1.3 Utilizing LEFT, LEN Functions

Just like the previous sub-method you can remove 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-
=LEFT(C5,LEN(C5)-2)

Where,

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

Formula of LEFT, and LEN to erase characters from left side

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

Final result removing special characters from the left hand side


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.

Sample dataset containing id number and names with special character

Steps:

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

Typing the name manually without special characters as preview for flash fill feature to fill other cells

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

Performing flash fill from the Home ribbon for other cells in the same column

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

Final output after removing special characters with flash fill command

Read more: How to Remove Blank Characters in Excel


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.

Sample dataset containing id number and names with special character

Step 1:

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

Copying data to another column with CTRL+C shortcut

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

Pasting data to another column with CTRL+V shortcut

Step 2:

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

Opening find and replace window with CTRL+F command

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

Entering special characters in the “Find what” field and replacing them with blanks by pressing replace all button

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

Confirmation of replaced characters from data table

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

Final output with find and replace feature by deleting specific characters

Read more: How to Remove Spaces in Excel: With Formula, VBA & Power Query


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

Opening Power Query window from data tab

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

Confirming data table with header for power query command

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

Addition of custom column from add column option

  • 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-
=Text.Select([NAME],{"A".."z","0".."9"})
  • Afterward, hit the OK button.

Applying formula for the newly created custom column and naming it

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

Pressing close & load to save the newly added column erasing characters

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

Final Output with texts after removing special characters using power query tool


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.

Pressing ALT+F11 to launch Visual Basic Application 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
Code Explanation:

In this section we’ll explain the VBA code used to remove specific characters in Excel.

  • In the first place, the function name is the function Erase_Special_Characters().
  • Then, define the two arguments Txt, xx, and yy as the String data type.
  • Afterward, use the For Next loop to apply the REPLACE and MID functions to loop through and erase the special characters with space.

Applying code for a defined function to erase special characters

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

Applying defined function to a cell to remove specific characters

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

Final output after removing characters using VBA code


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

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

2 Comments
  1. Thank you for the fantastic article, Syeda. After researching your comments about why the caret “^” characters were still remaining in D7 in your example above, I realized that the range {“A”..”z”} is actually using the Basic Latin Unicode Standard character list. So, since you’ve designated the range from capital A to lowercase z, that range would include several other characters a person might want removed if all they want is A-Z, a-z, and 0-9, such as [, \, ], ^, _, and `. If you modify the Text.Select argument from {“A”..”z”, “0”..”9″} to {“A”..”Z”, “a”..”z”, “0”..”9″}, the results will truly only select capital and lowercase letters and numbers without any special characters being overlooked. Thank you, again, for publishing this!

  2. It is effective. Thank you for providing this information.

Leave a reply

ExcelDemy
Logo