In Excel, while working with data, you might need to face some characters you don’t want. In this case, you can write a formula by using the SUBSTITUTE that can remove these unwanted characters. And in this tutorial, we will learn to write this formula.
Formula to Remove Unwanted Characters
Follow these steps to write this formula:
- First, in a cell, enter the SUBSTITUTE function.
- Next, refer to the cell where you have the original text.
- After that, enter the character you want to remove (use the double quotation marks).
- Now, enter a blank character using double quotation marks.
- In the end, hit enter to get the result.
=SUBSTITUTE(A1,"@"," ")This formula removes the “@” character from the value in cell A1 and replaces it with no character. Instead of replacing it with nothing, you can also use a blank space.
=SUBSTITUTE(A1,"@","")Using the SUBSTITUTE function is the quickest way to remove unwanted characters from a string. And if you want to remove two or more characters from a single cell, then you need to use the SUBSTITUTE function twice.
In the above example, you have three different characters that we don’t want to have in the text string. So now, you need to use a nested SUBSTITUTE to eliminate these characters.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@",""),"!",""),"-","")You have three SUBSTITUTE functions; the formula removes one unwanted character with each function.