How to Remove Specific Characters in Excel (5 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of removing specific characters


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!

Sample dataset with product name and product code


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.

Pressing CTRL+F to open Find and replace window

  • Next, from the appeared window put “#” in the “Find what” box and click “Replace All”.

Typing character to replace from cells

  • A confirmation window will pop up confirming all the replacements.
  • Simply, click OK.

Confirmation of replaced characters from cells

  • Finally, we have removed specific characters from the selected cells. Simple isn’t it?

Final output using the find and replace feature

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 “”.

Formula of SUBSTITUTE to remove characters

  • 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.

Final output using the SUBSTITUTE formula

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”.

Formula of REPLACE to remove characters

  • 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.

Final output using the REPLACE formula

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.

Combination of RIGHT and LEN functions to remove characters

  • To finish, click ENTER and drag down the Fill Handle.
  • In summary, we have successfully deleted specific characters in Excel.

Final output removing characters using the RIGHT and LEN functions

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.

Typing manually without characters to flash fill other cells

  • Then, choose the next cell (D6) and select Flash Fill from the Home Ribbon.

Visiting flash fill option from the Home ribbon

  • Within a glimpse, you will see the column is filled by erasing specific characters.

Final output deleting characters using the flash fill feature


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.

Pressing ALT+F11 to apply VBA code

  • 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

Applying VBA code inside a module

Code Explanation:

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.
  • Hence, coming back to the worksheet choose a cell (D5) and apply the defined function-
  • =del_char(C5,"#")

    Applying del-char VBA defined function to a cell

    • 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?

    Final output using VBA code removing unwanted characters


    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

    Prantick Bala

    Prantick Bala

    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

    Advanced Excel Exercises with Solutions PDF

     

     

    ExcelDemy
    Logo