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