Prantick Bala
Oftentimes, our Excel datasheet contains unnecessary characters which we may want to remove. In this article, I’ll show you 5 easy and simple ways how to remove specific characters in Excel.
The following image shows an overview of the article which represents the removal of specific characters in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Simple Ways to Remove Specific Characters in Excel
In the following, I have described 5 simple ways to remove specific characters in Excel. Follow the instructions below.
Let’s say, we have different product codes for different products in column B which are started with the character “#”. Now we will remove this specific character using some easy tricks. Stay tuned!
1. Using Find and Replace Feature to Remove Specific Characters
You can remove specific characters from multiple cells at a time by using Find and Replace command. Follow the instructions below.
Steps:
- First, select a range of cells from the data table and press CTRL+F to open the “Find and Replace” window.
- Next, from the appeared window put “#” in the “Find what” box and click “Replace All”.
- A confirmation window will pop up confirming all the replacements.
- Simply, click OK.
- Finally, we have removed specific characters from the selected cells. Simple isn’t it?
Read more: How to Remove Special Characters in Excel
2. Removing Specific Characters with SUBSTITUTE Function
Using the SUBSTITUTE function is another way to remove specific characters from a cell.
Steps:
- Just choose a cell (D5) and apply the below formula down-
=SUBSTITUTE(C5, "*&#", "")Where,
- The SUBSTITUTE function is replacing “*&#” characters from string “*ϩ”.
- Simply hit the ENTER key and drag the Fill Handle down to fill.
- In conclusion, you will get the final output in your hands by removing specific characters.
Read more: How to Remove Non-Printable Characters in Excel
3. Applying REPLACE Function
You can use the REPLACE function to remove any character from any cell. Go through the steps below-
Steps:
- Similarly, choose a cell (D5) and write the below formula down-
=REPLACE(C5,1,1,"")Where,
- The REPLACE function replaces characters (#) from the given string “#1001”.
- Just click ENTER and pull the Fill Handle down to fill the other cells.
- Within a blink of an eye, you will get all the specified characters removed.
Read more: How to Remove Characters in Excel
4. Combining RIGHT and LEN Functions to Remove Specific Characters
With the combination of RIGHT and LEN functions you can remove specific characters too.
Steps:
- In the same fashion, select a cell (D5) and put the below formula in the cell-
=RIGHT(C5,LEN(C5)-1)Where,
- The LEN function is returning the length of the chosen cell (C5).
- The RIGHT function then extracts a specified number of text from right to left. Thus the output stands as –1001.
- To finish, click ENTER and drag down the Fill Handle.
- In summary, we have successfully deleted specific characters in Excel.
Read more: How to Remove First Character in Excel
5. Utilizing Flash Fill to Erase Specific Characters
Using the Flash fill is another simple way to erase specific characters. In the below method, I have deleted specific characters utilizing the flash fill option.
Steps:
- Simply, choose a cell (D5) and type the values from the cells without the specific characters.
- Then, choose the next cell (D6) and select Flash Fill from the Home Ribbon.
- Within a glimpse, you will see the column is filled by erasing specific characters.
How to Remove Unwanted Characters in Excel
Often working with a large dataset you will get to see some unwanted characters which are not desirable. Removing those manually is a hectic job. Well, you can delete all those unwanted characters with a simple trick. Please go through the steps below to learn-
Steps:
- While opening the workbook press ALT+F11 to open the “Microsoft Visual Basic for Applications” window.
- Now, open a module and place the following code, and press “Save”-
Function del_char(txt As String, chr As String)
If ("" <> chr) Then
txt = Replace(txt, Left(chr, 1), "")
chr = Right(chr, Len(chr) - 1)
del_char = del_char(txt, chr)
Else
del_char = txt
End If
End Function
In this section, we’ll explain the VBA code used to remove specific characters in Excel.
- In the first place, the function name the function del_char().
- Then, define the two arguments txt and chr as the String data type.
- Afterward, use a If Then statement and Replace function to switch the specified character with a white space character.
=del_char(C5,"#")
- Simply, press the ENTER key and drag the Fill Handle down to fill.
- Finally, we have removed unwanted characters from all the cells. Simple isn’t it?
Conclusion
In this article, I have tried to cover almost all the methods on how to remove specific characters in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
Further Readings
- How to Remove Spaces in Excel: With Formula, VBA & Power Query
- Remove the First Character from a String in Excel with VBA
- Remove Last Character from String Excel (5 Easy Methods)
- VBA to Remove Characters from String in Excel (7 Methods)
- Remove the Last 3 Characters in Excel (4 Formulas)
- How to Remove Characters from Left in Excel (6 Ways)
