You can use the following formula to remove special characters from a cell in Excel:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"!",""),"@",""),"#",""), "$", ""), "%", ""), "^", ""), "&", ""), "*", ""), "(", ""), ")", "")
This particular formula removes all special characters from cell A2.
This formula works by using nested SUBSTITUTE functions to substitute specific special characters with blanks, which has the effect of removing special characters from a cell.
Note: If you’d like to remove additional special characters from a cell, simply use additional nested SUBSTITUTE functions.
The following example shows how to use this formula in practice.
Example: Remove Special Characters in Excel
Suppose we have the following list of phrases in Excel:
Suppose we would like to remove the special characters from each phrase in column A.
To do so, we can type the following formula into cell B2:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"!",""),"@",""),"#",""), "$", ""), "%", ""), "^", ""), "&", ""), "*", ""), "(", ""), ")", "")
We can then click and drag this formula down to each remaining cell in column B:
The cells in column B contain the phrase from each corresponding cell in column A with the special characters removed.
Note that you can easily modify this formula to only remove certain special characters from cells.
For example, you could use the following formula to only remove !, @ and # from the cells:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"!",""),"@",""),"#","")
Feel free to modify the formula to remove whichever special characters you’d like from cells.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Search for an Asterisk in a Cell in Excel
How to Search for a Question Mark in Excel
How to Search for Special Characters in a Cell in Excel
