Mukesh Dipto
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.
Below we showed an example of removing special character using the Flash Fill tool.
Download Practice Workbook
You can download the workbook and practice with them.
5 Easy Methods to Remove Specific Character from String in Excel
Here, we are going to present 5 separate easy methods following which you can remove specific character from string in Excel. In order to avoid any incompatibility issues, try to use the Excel 365 edition.
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.
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.
- 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.
- A dialog box named Find and Replace will open.
- 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,"#","")
- Now press Enter. We will get the value of cell B5 without “#” in cell C5.
- Select cell C5 and hover the cursor at the bottom right corner of that cell and we will see the Fill Handle tool
- 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,"")
- Next, press Enter. We will get the value of B5 free from 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
- Remove Dashes from Phone Number in Excel(4 Ways)
- How to Remove Dashes in Excel (3 Methods)
- Remove Hidden Double Quotes in Excel (6 Easy Ways)
- How to Remove Semicolon in Excel (4 Methods)
- How to Remove Apostrophe in Excel (5 Easy Methods)
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)
- Next, press enter. You will get the value of cell B5 free from the “#” character.
- After that, select cell C5. Hover the cursor at the bottom right corner to get the Fill Handle.
- 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
Counts the number of characters in Cell B5 one less than the actual number. As a result, its value becomes 9.
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 ‘#‘.
- Next, go to the Fill option. From the drop-down menu hover over Flash Fill.
- 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
- How to Remove Blank Characters in Excel (5 Methods)
- How to Remove Non-Printable Characters in Excel (4 Easy Ways)
- VBA to Remove Characters from String in Excel (7 Methods)
- How to Remove Non-numeric Characters from Cells in Excel
- Remove First Character from String in Excel (6 Easy Ways)
- How to Remove Characters from Left in Excel (6 Ways)
