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-
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,"#",""),"!",""),"$",""),"%",""),"&","")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 characters in case of recursive characters present in the text.
- 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-
=RIGHT(C5,LEN(C5)-1)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-
=LEFT(C5,LEN(C5)-2)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-
=Text.Select([NAME],{"A".."z","0".."9"})- 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 FunctionIn this section, we’ll explain the VBA code used to remove special 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.
- Now, coming back to the worksheet, choose a cell (D5) and write the below formula down-
=Erase_Special_Characters(C5)
- 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
<< Go Back To Excel Remove Characters | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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!
It is effective. Thank you for providing this information.