Remove Specific Character from String Excel (5 Easy Methods)

When working with text data in Excel we may have to remove an unnecessary specific character from string excel. To work conveniently we need to remove those characters from our data. In this tutorial, we will look at the five most common ways to remove characters from string excel.


Download Practice Workbook

You can download the workbook and practice with them.


5 Easy Methods to Remove Specific Character from String Excel

1. Remove Specific Character from String Excel Using Find and Replace Options

In excel, the Find and Replace options find a character and replace it with another set of characters. Suppose we have the following dataset of six product codes and we have to remove the specific character “#” from all the string values in Excel using Find and Replace options.

Remove Specific Character From String Excel Using Find and Replace Options

We will solve this problem by following the below steps.

STEPS:
First, select cells B5 to B10 from where we want to remove the “#” character.

Remove Specific Character From String Excel Using Find and Replace Options

Then press Ctrl + H or go to the Home tab and click on the Find & Select command under the Editing option. Select Replace from the drop-down menu.

Remove Specific Character From String Excel Using Find and Replace Options

A dialog box named Find and Replace will open.

Remove Specific Character From String Excel Using Find and Replace Options

Next, put the character “#” in Find what box which you want to remove and keep the Replace with box empty. After that, click on Replace All.

Click on OK in a pop-up window named Microsoft Excel.

Finally, there will remain no “#” character.

Read More: How to Remove Character from String in Excel (14 Ways)


2. Use of SUBSTITUTE Function to Remove Specific Character from String Excel

We can use another way to remove the specific character ‘#‘ from the string excel called the SUBSTITUTE function. The SUBSTITUTE function replaces text in a given string. To do this we will use the same dataset that we have used in Method-1.
STEPS:
Firstly, we will create a new column titled ‘Cleaned Data’ in column C.

Next, select cell C5. write down the following formula:

=SUBSTITUTE(B5,"#","")

Use of SUBSTITUTE Function to Remove Specific Character

Now press Enter. We will get the value of cell B5  without “#” in cell C5.

Use of SUBSTITUTE Function to Remove Specific Character

Select cell C5 and hover the cursor at the bottom right corner of that cell and we will see the Fill Handle tool

Use of SUBSTITUTE Function to Remove Specific Character

Finally, drag the Fill Handle of cell C5  to the end of the dataset which is cell C10. By doing this we will be able to remove the “#” character from the values of the product code column.

Read More: Remove Characters after a Specific Character in Excel (4 Tricks)


3. Utilize REPLACE Function to Remove Specific Character from String Excel

The REPLACE function is another effective way to remove a specific character from string excel. The difference between the SUBSTITUTE function and REPLACE function is that the SUBSTITUTE function replaces one or more instances of a given text string whereas the REPLACE function replaces text in a specified position of a supplied string. Let’s see how you can use the REPLACE function.
We will use the same data that was used earlier to demonstrate this method.
STEPS:
Firstly, select cell C5 and write down the following formula:

=REPLACE(B5,1,1,"")

Replace Function to Remove Specific Character

Next, press Enter. We will get the value of B5 free from character “#”.

Replace Function to Remove Specific Character

After that, select cell C5 and drag the Fill Handle tool to the end of the dataset which is cell C10.

Finally, we can see the dataset without the ‘#‘ character.

Related Content: How to Remove First 3 Characters in Excel (4 Methods)


Similar Readings:


4. Remove Specific Character from String Excel Using RIGHT and LEN Functions

The RIGHT function returns the last character or characters in the text string and the LEN function counts the characters in the cell. One more way to remove specific character from string excel is using a combination of the RIGHT function and LEN function.
We have the dataset of previous methods and we will continue with the same dataset here also.
STEPS:
➤ Firstly, we have to select cell C5 and input the following formula:

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

Remove Specific Character from String Excel Using Right and Len Functions  

Next, press enter. You will get the value of cell B5 free from the “#” character.

Remove Specific Character from String Excel Using Right and Len Functions  

After that, select cell C5. Hover the cursor at the bottom right corner to get the Fill Handle.

Remove Specific Character from String Excel Using Right and Len Functions  

➤ Finally, drag the Fill Handle tool to the end of the dataset which is cell C10. As a result, we will get all the values of the Product Code column free from the “#” character.

➥ Formula BreakDown

LEN(B5)-1
Counts the number of characters in Cell B5 one less than the actual number. As a result, its value becomes 9.

RIGHT(B5,LEN(B5)-1)
It returns the last 9 characters from Cell B5.

Read More: How to Remove the First Character from a String in Excel with VBA


5. Use of Flash Fill to Remove Specific Character from String Excel

One more way to remove specific character from string excel is using the Flash Fill. Flash Fill is a special tool that analyzes the information we are entering and automatically fills data when identifying a pattern. We have the dataset of previous methods and we will continue with the same dataset here also.
STEPS:
➤ Firstly, select cell C5 and input manually the value of cell B5 without the character ‘#‘.

Use of Flash Fill to Remove Specific Characters from String Excel 

➤ Next, go to the Fill option. From the drop-down menu hover over Flash Fill.

Use of Flash Fill to Remove Specific Characters from String Excel 

➤ Finally, click on the Flash Fill. As a result, we will get all the values of the product code column without the character “#”.

Related Content: Remove Last Character from String Excel (5 Easy Methods)


Conclusion

In this article, we have learned about the 5 ways of removing specific characters from string excel. So, go ahead and practice these methods yourself and if you face any problem during your practice just leave a reply in the below box. Stay tuned with us for more Excel problems and solutions.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo