8 Ways to Remove Symbols in Excel

Method 1: Using the Find and Replace Symbol

Example Model:

Note: We will remove the ‘#’ symbol with Find and Replace.

Steps:

Select the range of cells you want to remove symbols from.

In the Home tab, go to the Editing group and select Find & Select.
In the drop-down menu, select Replace.

A Find and Replace box will appear. In the Find What box, type # and leave the Replace with box empty.
Click on Replace all (or you can press Ctrl+H and skip the last two steps).

Note: All ‘#’ symbols have been removed from selected cells.

Note: If you want to further remove symbols, repeat the same process for different ones.

Method 2: Applying the Excel SUBSTITUTE Function to Remove Symbols

Steps:

Select cell C5 and write the following function.

=SUBSTITUTE(B5,"#","")

Press “Enter, the ‘#’ symbol will be removed from the cell.

Click the Fill Handle icon on the bottom right of the box and drag it down.

Method 3: Using the SUBSTITUTE Function to Remove Multiple Symbols

Note: We will remove the (#,/,€) symbols with Find and Replace.

Steps:

Enter the following formula into cell

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"#",""),"/",""),"€","")

Press Enter.

Grab the fill handle icon on the cell and drag it to the end of the list.

Breakdown of the Formula:

SUBSTITUTE(B5,”#”,””) removes all the # symbol in the cell B5.

Then SUBSTITUTE(SUBSTITUTE(B5,”#”,””),”/”,””) removes all the “/” symbol from the cell.

After that SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,”#”,””),”/”,””),”€”,””) removes all the € symbol from the cell.

Method 4: Removing Symbols with the SUBSTITUTE, CODE and CHAR Functions

Example Model:

Steps:

Select cell C5.
Enter the following formula into the cell

=SUBSTITUTE(B5,CHAR(CODE(B5)),"")

Press Enter.
Click the Fill Handle Icon in the cell and drag it down to remove the symbols.

Method 5: Using the Flash Fill Feature to Remove Symbol

Example Model:

Steps:

Write down the first cell without the symbols.

Depending on the combination of numbers and letters, a suggestion box will appear. For this dataset, you need 2 more.

Once the suggestion box appears, press Enter.

Method 6: Inserting the LAMBDA Function to Remove Symbol

Note: Only for Microsoft Office 365 users.

Example Model:

Steps:

Select the Formulas tab, under the Defined Names group, select Name Manager.

In the Name Manager box, select

In the New Name box, type RemSym, Enter the following formula into the RemSym box.

=LAMBDA(string,chars, IF(chars<>"", RemSym(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))

Click on OK and close the Name Manager window
Go to the cell, and type the following formula.

=RemSym(B5,$E$5)

Click and drag the Fill Handle icon to the end of the list (all the symbols in cell E5 will be removed.

Method 7: Removing Non-Printable Symbols

Example Model:

Steps:

Select cell C5.
Enter the following formula into the cell.

=CLEAN(B5)

Press Enter.

Click and drag the Fill Handle icon to create the rest of the cells in the list.

Method 8: Applying VBA Macro to Remove Symbols

Example Model:

Steps:

Go to the Developer tab and select Visual Basic from the Code

In the Visual Basic window, select Insert and then select Module from the drop-down list.

Enter the following formula into the module.

Function RemoveSymbols(str As String) As String

Dim symb As String
Dim index As Long

symb = "?¿!¡*%#$(){}[]^&/\~+-|€<>"
For index = 1 To Len(symb)
str = Replace(str, Mid(symb, index, 1), "")
Next
RemoveSymbols = str

End Function