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.

selecting cells to remove symbol in excel

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

selecting find & replace feature to remove symbol in excel

  • 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).

how to remove symbol from find and replace box

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

how to remove symbol

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.

using substitute function to remove symbol in excel

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

substitute function result


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.

nested substitute function to remove symbol in excel

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

removing multiple symbol using nested substitute function

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)),"")

how to remove symbol in excel using code and char function

  • 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.

how to remove symbol in excel using flash fill feature

  • Once the suggestion box appears, press Enter.

flash fill feature result

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))

naming function

  • 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.

dragging fill handle icon down for lambda function


Method 7: Removing Non-Printable Symbols

Example Model:

Steps:

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

=CLEAN(B5)

  • Press Enter.

how to remove non printable symbol in excel

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

non printable symbol removed


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.

how to remove symbol in excel using vba

  • Click and drag the Fill Handle icon to fill in the rest of the cells.

auto filling vba formula down


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!
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo