How to Remove Specific Characters in Excel ( 5 Ways)

Remove specific characters in excel

Sometimes our Excel datasheet contains unnecessary characters which we may want to remove. In this article, I’ll show you five easy and simple ways to remove specific characters in Excel.

Let’s say, we have different product codes for different products in column B which are started with a character “#”. We want to remove this specific character.

dataset

Download Practice Workbook

5 Ways to Remove Specific Characters in Excel

1.   Remove Specific Characters by Find and Replace

You can remove specific characters from multiple cells at a time by using Find and Replace Command. First, select all the cells from where you want to remove this character. Then, go to Home> Editing> Find & Select > Replace

Find and replace

After that, a Find and Replace box will appear. Type the character you want to remove in the Find what box. Keep the Replace with box empty. Lastly, click Replace All.

Remove specific characters in excel

After giving the command excel will remove the character from your selected cells. A dialog box will appear showing the number of the replacement made. Click OK in this dialog box and close the Find and Replace box.

replace all

Now, you will see all the characters are removed. So, we will get the product code for different products without the character “#”.

DATA without specific characters

Read more: How to Remove Special Characters in Excel

2.   Remove Specific Characters by SUBSTITUTE Function

Using the SUBSTITUTE function is another way to remove specific characters from a cell. Type the following formula in an empty cell,

=SUBSTITUTE(B6, "#", "")

 Here, B6 refers to the cell from where you want to remove the character, “#” is the specific character and ” ” indicates that you are removing that character.

SUBSTITUTE FUNCTION

After pressing ENTER, you will get the text without the character in the cell in which you’ve typed the formula.

SUBSTITUTE

Drag cell C6 till the end of your dataset to get all the product code without the specific character.

Remove specific characters in excel

You can also remove multiple characters at a time by using the SUBSTITUTE function. Suppose you have “*&#” before your product code. To remove this, type the following formula in cell C6

=SUBSTITUTE(B6, "*&#", "")

After pressing Enter and dragging cell C6, you will get the product code without any specific characters.

REMOVE MULTIPLE CHARACTERS

Read more: How to Remove Non-Printable Characters in Excel

3.   REPLACE Function to Remove Specific Characters

You can use the REPLACE function to remove any character from any cell. Type the following formula in an empty cell,

=REPLACE(B6,1,1,"")

Here, B6 refers to the cell from which you want to remove the character, First 1 indicates that you want to remove the character from the first character of your text, Second 1 indicates that you want to remove one character, and ” ” indicates that you are removing that character.

replace

After pressing ENTER, you will get the text without the character in the cell in which you’ve typed the formula.

replace function

Drag cell C6 till the end of your dataset to get all the product code without the specific character.

Remove specific characters in excel

Read more: How to Remove Characters in Excel

4.   RIGHT and LEN Functions to Remove Specific Characters

Using the RIGHT function and the LEN function altogether, you can remove any specific character from a cell. Type the following formula in an empty cell,

=RIGHT(B6,LEN(B6)-1)

Here, B6 refers to the cell from which you want to remove the character, RIGHT function indicates that the characters of the text will be taken from the last character, and LEN(B6)-1 indicates that the resultant text will be without the first character of the referred text (B6).

Right and Len function

After pressing ENTER, you will get the text without the character in the cell in which you’ve typed the formula.

right and len

Drag cell C6 till the end of your dataset to get all the product code without the specific character.

Remove specific characters in excel

Suppose you have double “#” character in your product code and you want to remove only one of them. You can do it by using the RIGHT and LEN function. Type the following formula in an empty cell,

=RIGHT(B6,LEN(B6)-1)

After that, drag the cell C6  to the end of your dataset and you will get all the product codes with only one “#”

Remove specific characters in excel

Read more: How to Remove First Character in Excel

5.   Remove Specific Characters with Flash Fill

Using Flash Fill is another option to remove any specific characters from multiple cells.

First manually type the product code without the character in the first cell and select all the cells where you want to apply Flash Fill. Then go to Home> Editing> Fill> Flash Fill

Flash Fill

By clicking on the Flash Fill, you will get all the product codes without the specific characters.

Remove specific characters in excel

Conclusion

If you have the specific Characters in random cells in your Excel sheet, using Find and Replace is the best way. If you want to remove the specific characters from any particular column, using Flash Fill is more convenient. But if you want to change different specific characters from different cells, using any of the formulas such as SUBSTITUTE, REPLACE, or RIGHT and LEN will be a better option.

If you face any problem while removing specific characters or have any confusion about any methods described in this article, please feel free to leave a comment.


Further Readings:

Prantick

Hi, I'm prantick bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo