How to Remove Symbol in Excel (8 Ways)

Get FREE Advanced Excel Exercises with Solutions!

There could be so many unwanted symbols and characters existing in Excel cells, maybe from a typing mistake, or a symbol that was needed initially and you don’t need anymore. Excel provides some excellent tools to remove them from a spreadsheet. This article will focus on these different methods and tools to remove symbols in Excel.


Below, I will be going over 8 different methods, each having its unique uses in different scenarios. I will be going over the pros of them and when to use each in their respective part and a detailed guide on how to do them. Also, I will be using different datasets to demonstrate different methods as each one is suitable for different purposes.


1. Using Find and Replace to Remove Symbol in Excel

This may be the fastest and easiest way to remove symbols from a group of Excel cells. The downside is you have to remove each symbol one by one. Nevertheless, It is a very sound method when you have the same repeating symbol every time in a group of cells.

For this method, I have used the dataset below.

From this dataset, I will be removing the ‘#’ symbol with Find and Replace.

Steps:

  • First, select the range of cells you want to remove symbols from.

selecting cells to remove symbol in excel

  • Then, in the Home tab, go to the Editing group and select Find & Select.
  • In the drop-down list, 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 Replace with box empty.
  • Then 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

You will now have the ‘#’ symbol removed from all the selected cells.

how to remove symbol

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


2. Applying Excel SUBSTITUTE Function to Remove Symbol

If using function is your thing, this method might be the right one for you. You can achieve the very same result from above, from the same dataset, by using the SUBSTITUTE function.

The SUBSTITUTE function takes three arguments, the text to substitute from, the string to substitute, and the string to replace with.

I will be using the same dataset from the previous method to demonstrate this method and will be removing the ‘#’ sign from the cells.

Steps:

  • First, select cell C5 and write the following function.
=SUBSTITUTE(B5,"#","")

  • Now, press Enter on your keyboard. You will have the ‘#’ symbol removed from the reference cell’s content.

using substitute function to remove symbol in excel

  • After that, click on the Fill Handle icon on the bottom right of the box and drag it down.

You will have your complete data filled with the symbol removed.

substitute function result


3. Using Nested SUBSTITUTE Function to Remove Multiple Symbols

This method is suitable for removing multiple symbols using functions. In this method, I will show you how to use nested loops to do that.

To demonstrate this, I will be using the following dataset. I will be using the SUBSTITUTE function, nested with as many symbols as I want to remove, which is three in my dataset (#,/,).

Steps:

  • First, select the cell you want to put the result in which is cell C5 in this case.
  • Then write down the following function.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"#",""),"/",""),"€","")
  • Press Enter on your keyboard.

nested substitute function to remove symbol in excel

  • Now, grab the fill handle icon bar on the cell and drag it to the end of the list and you will have the symbols removed.

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.


4. Removing Symbol with SUBSTITUTE, CODE and CHAR Functions

Instead of the previous one, you can also use a combination of the CODE, CHAR, and SUBSTITUTE functions to remove the symbol from a reference cell. This method comes in handy for the symbols you can’t type in and you don’t know the code values of.

The CODE function is used to find out the ASCII value of a character or the first character of a text. The CHAR function is helpful to write the special characters in Excel. The combination of these two will help the SUBSTITUTE function to put in the argument of a replaceable symbol in the string.

For a demonstration of this method, I will be using the following dataset. I will be removing the © symbol from these cells.

Steps:

  • First, select cell C5.
  • The write-in the following formula in the cell.
=SUBSTITUTE(B5,CHAR(CODE(B5)),"")

how to remove symbol in excel using code and char function

  • Press Enter on your keyboard.
  • Click on the Fill Handle Icon in the cell and drag it down to have the symbol removed from all the cells.

🔍 Breakdown of the Formula:

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

👉 CODE(B5) returns the ASCII number of the first value of cell B5.

👉 CHAR(CODE(B5)) returns the symbol that we are targeting to remove, ©.

👉 SUBSTITUTE(B5, CHAR(CODE(B5)),””) removes the symbol from the text, replacing © with an empty string.


5. Using Flash Fill Feature to Remove Symbol

You can remove symbols from a group of cells using the Flash Fill feature of Excel. This is a very user-friendly and fast method. But, this method can only be applied if all of the cells in the range have the same pattern for every symbol.

To demonstrate this method, I will be using the following dataset with the same pattern of symbols.

To use the Flash Fill feature easily, follow these simple steps.

Steps:

  • Write down the first cell manually without the symbols.

  • Depending on the combination of numbers and letters, you use to write one or two more of the cells manually 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

You will have all the cells filled up by the Flash Fill feature.

Read More: How to Remove Sign from Numbers in Excel


6. Inserting LAMBDA Function to Remove Symbol

This method can only be used if you have Microsoft Office 365. Excel 365 enables you to create your custom function to reuse it later on. I will be using a combination of the LAMBDA, IF, SUBSTITUTE, LEFT, RIGHT, and LEN functions to create the reusable function.

The LAMBDA function can take multiple arguments, the last one being the calculation of the parameters and the previous ones as the parameters. The IF function takes three arguments- a condition, a value to show if the condition is true, and another one if it is false. The SUBSTITUTE function replaces the second argument with the third in the first argument. The LEFT and RIGHT functions pick up some characters from the start or end of a text and the LEN function displays the length of a given text.

To demonstrate, I have picked up the following dataset with the removable symbols in a particular cell.

Steps:

  • From the ribbon, select the Formulas tab, and under the Defined Names group, select Name Manager.

  • In the Name Manager box, select New.

  • In the New Name box, type RemSym in the name field, and in the Refers to field put in the following formula.
=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 dialog.
  • Now go to your cell, and type in the following formula.
=RemSym(B5,$E$5)

  • After that click and drag the Fill Handle icon to the end of the list and have all the symbols written in cell E5 in the range removed.

dragging fill handle icon down for lambda function

🔍 Breakdown of the Formula:

The formula we have used in this LAMBDA function is

IF(chars<>””, RemSym(SUBSTITUTE(string, LEFT(chars, 1), “”), RIGHT(chars, LEN(chars) -1)), string).

Here is a breakdown of it for the first cell.

👉 LEFT(E5, 1) function takes the first character from cell E5.

👉 SUBSTITUTE(B5, LEFT(chars, 1), “”) function removes the first symbol from E5 and gives output e1€4.

👉 RIGHT(E5, LEN(E5) -1) function takes the rest of the characters from cell E5 and has the output /?€|\><.

👉 The RemSym(SUBSTITUTE(B5, LEFT(E5, 1), “”), RIGHT(E5, LEN(E5) -1)) is a custom function here and has the output e14.

👉 Now, the IF(B5<>””, RemSym(SUBSTITUTE(B5, LEFT(E5, 1), “”), RIGHT(E5, LEN(E5) -1)), B5) function gives the output e14 for this cell.

👉 The LAMBDA function here helps to put in the values of range B5:B9 as string values and cell E5 as symbol values.


7. Removing Non-Printable Symbol in Excel

If it is all the non-printable symbols you want to remove from a range of cells, the CLEAN function is all you need. This function takes a text and clears all the nonprintable characters from the text. Follow the steps shown below for more detailed instructions.

I have used the following dataset with CHAR(8) in it, which is nonprintable.

Steps:

  • First, select cell C5.
  • Then type in the following formula in the cell.

=CLEAN(B5)

  • Now, 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


8. Applying VBA Macro to Remove Symbol

You can also use VBA to create a custom function so that you can remove the symbols from a cell. To do this, you need the Developer tab in the ribbon. Click here to see how to enable the developer tab.

I have used the following dataset for demonstration.

Once you have the tab shown, you can follow the steps below.

Steps:

  • First, go to the Developer tab and select Visual Basic from the Code group.

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

  • In the module write the following formula.
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
  • Now, save it and go back to your cell.
  • Then type in the following formula in cell C5.
=RemoveSymbols(B5)
  • Press Enter.

how to remove symbol in excel using vba

  • Click and drag the Fill Handle icon to fill out 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.


Conclusion

These were the different methods you can use to remove symbols in Excel. Hope you found this guide helpful and that it was of use to your context. If you have any suggestions or questions let us know below.


Remove Symbol in Excel: Knowledge Hub


<< Go Back to Excel Symbols | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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