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.
Read More: How to Remove Sign from Numbers in Excel
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
- Save and go back to the cell.
- Enter the following formula into cell C5.
=RemoveSymbols(B5)
- Press Enter.
- Click and drag the Fill Handle icon to fill in the rest of the cells.
Download Practice Workbook
You can download the workbook that I have used for demonstrating the article with all the datasets from the download box below.
Remove Symbol in Excel: Knowledge Hub
<< Go Back to Excel Symbols | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!