Go Back

Remove unwanted characters

It is common to find yourself in a position where you need to remove unwanted characters. You can do this very easily in Microsoft Excel with the SUBSTITUTE function. In this tutorial, you will learn how to remove unwanted characters in Excel.

How to Remove Unwanted Characters in Excel

You can use the SUBSTITUTE function in Excel to remove unwanted characters. The following example contains a student data set. Column A has the student names, column B has the old ids and column C has the new ids. Here, you will be removing the first two characters representing a # from the ids. To do this:

  • Go to cell C2. Select it with your mouse.
  • Assign the formula, =SUBSTITUTE(B2,CHAR(CODE(LEFT(B2))),"") to cell C2. The SUBSTITUTE function uses the syntax.=SUBSTITUTE( text, old_text, new_text, [nth_appearance]). Here, B2 is used as the first argument which is the text where you want to remove the character. LEFT function selects the leftmost character from the left. This is nested inside a CODE function which returns the character value of the leftmost character. This goes inside a CHAR character which returns the character based on the value from the CODE function. This whole section is provided as the argument of the SUBSTITUTE function and the third argument is an empty string (“”). This removes the # with an empty string.
  • Press Enter to apply the formula to C2.
  • Drag the fill handle from cell C2 to C6 to apply the formula to the entire column.

                                                  Figure 1. Removing Unwanted Characters in Excel 

This will remove the # characters from the ids.

The SUBSTITUTE function helps to remove unwanted characters very easily from strings. With the help of some other functions, you can remove unnecessary chunks of text. This comes in very handy when working with text.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hey i need to remove unwanted data in the x-axis on a graph
Solved by O. Q. in 25 mins
remove specifics characters on "" "" with specials find replace with ";"
Solved by F. J. in 20 mins
MY =RIGHT FORMULA TO REMOVE CHARACTERS IN EXCELL IS NOT WORKING
Solved by I. D. in 19 mins

Leave a Comment

avatar