How to Remove Symbol in Excel (8 Ways)

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 symbol in Excel.


Download Practice Workbook

You can download the workbook which I have used for demonstrating the article with all the datasets from the download box below.


8 Ways to Remove Symbol 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. Remove Symbol Using Excel Find and Replace

This maybe is the fastest and easiest way to remove symbol 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 symbol from.

how to remove symbol

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

  • 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

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.

Read More: How to Remove Plus Sign in Excel (2 Easy Techniques)


2. Applying SUBSTITUTE Function

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.

how to remove symbol

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

how to remove symbol


3. 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 as many symbols 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.

how to remove symbol

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

how to remove symbol

🔍 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. Combining SUBSTITUTE Function with 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 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

  • 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. 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 and a suggestion box will appear. For this dataset, you need 2 more.

how to remove symbol

  • Once the suggestion box appears, press Enter.

how to remove symbol

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

Read More: How to Remove Sign from Numbers in Excel (6 Suitable Methods)


6. Utilizing LAMBDA Function in Excel

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

how to remove symbol

  • Click on OK and close the Name Manager
  • 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.

how to remove symbol

🔍 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. Remove Non-Printable Symbol

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 symbol

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

how to remove symbol


8. Embedding VBA Codes 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)
  • And press Enter.

how to remove symbol

  • Click and drag the Fill Handle Icon to fill out the rest of the cells.

how to remove symbol


Conclusion

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

For more detailed guides visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo