How to remove specific characters in Excel

Microsoft Excel is an excellent tool for organizing and analyzing data. It is widely used by businesses, students, and professionals alike to create spreadsheets, charts, and tables. However, sometimes you may need to Remove Specific Characters from your data, which can be a tedious task if done manually. In this tutorial, we will explore different methods on how to remove specific characters in Excel.

Here we have a random dataset, in this dataset, there are characters of different types and we need to remove a specific character, the ‘#’ character.  In this tutorial, we will learn how to remove specific characters from a dataset but first let’s take a look at Dataset above.

Method – 1 Using Substitute Formula.

Step – 1 Using the SUBSTITUTE function.

• Select the column which contains the character to be removed.
• Select the column where you want to type the formula.
• The Syntax of the formula will be

=SUBSTITUTE(Cell_Address, “Character_To_Replace_With”, “Value_To_Replace_With”)

• In our case, the formula will be

=SUBSTITUTE(A2, “#”, “”)

Step – 2 Find the values of the remaining cells.

• Select the cell containing the formula.
• Drag the cell from the bottom right to the rest of the cells.
• The formula will then be applied automatically.

Method – 2 Using Find and Replace.

Step – 1 Choose the value to find.

• Choose the value to which you want to replace in our case ‘#’.
• Press Ctrl + H.
• The Find and Replace dialog box will appear.
• In the dialog box click on the Replace command.

Step – 2 Replace the value.

• In the Find what: box type the value you want to replace.
• Then click on Replace All.
• The value will be replaced.