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.

“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!”
- - Chris T, in California

Leave a Comment

This comment form is under antispam protection

This comment form is under antispam protection

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Trusted by people who work at

Plans & Pricing

I’ll use as many sessions as I want per month

$29.99

/month

Unlimited Sessions

Best offer

I want the unlimited sessions for 3 months AND save money

$74.99

/ 3 months

Unlimited Sessions for 3 months

Save $15 when you buy a subscription for 3 months

I want the unlimited sessions subscription AND save over a hundred dollars