Exceljet

Quick, clean, and to the point

Remove unwanted characters

Excel formula: Remove unwanted characters
Generic formula 
=SUBSTITUTE(B4,CHAR(code),"")
Summary 

To remove specific unwanted characters in Excel, you can use a formula based on the SUBSTITUTE function. In the example shown, the formula in C4 is:

=SUBSTITUTE(B4,CHAR(202),"")

Which removes a series of 4 invisible characters at the start of each cell in column B.

Explanation 

The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which effectively removes the character completely.

How can you figure out which character(s) need to be removed, when they are invisible? To get the unique code number for the first character in a cell, you can use a formula based on the CODE and LEFT functions:

=CODE(LEFT(B4))

Here, the LEFT function, without the optional second argument, returns the first character on the left. This goes into the CODE function, which reports the characters code value, which is 202 in the example shown.

For more general cleaning, see the TRIM function and the CLEAN function.

All in one formula

In this case, since we are stripping leading characters, we could combine both formulas in one, like so:

=SUBSTITUTE(B4,CHAR(CODE(LEFT(B4))),"")

Here, instead of providing character 202 explicitly to SUBSTITUTE, we are using CODE and CHAR to provide a code dynamically, using the first character in the cell.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.