Excel CLEAN Formula

Ever copy and paste some data from a web page, or import it from another program, and end up with some funky and usually unwanted characters like these nonprinting characters below?

The Excel CLEAN Function can help you to remove them, but it has some limitations.

You see it was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. Blah, blah, blah… Sorry, let me give you an example before you fall asleep.

Excel CLEAN Function Syntax

It’s pretty straight forward:

=CLEAN(text)

Where ‘text’ can be a reference to the cell containing the text you want to clean. See the example in cell E2 below:

Warning: more boring techy talk coming up, but I know some of you will want to know this stuff.

The CLEAN Function Can’t Do It All

In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).

Plus if you’re familiar with HTML then you’ve probably stumbled across the HTML entity;   which is character 160.

To remove these you need to use the SUBSTITUTE function.

SUBSTITUTE Function

The SUBSTITUTE function replaces new_text for old_text in a text string.

The syntax is slightly more complicated that the CLEAN Function but nothing drastic:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

The arguments are as follows:

• text – cell containing the characters you want to replace.
• old_text – the text to be replaced.
• new_text – the new text you want inserted.
• Instance_num – if omitted, it replaces every occurrence of the old_text; Otherwise, only the specified occurrence of the old_text is replaced in the text string.

It can do everything CLEAN can do and more like this:

Taking the formula displayed in cell C4 above, in English it reads:

Locate the text in cell A4, and find the character number 127, and replace it with nothing (as denoted by the two sets of double quotes).

Of course with SUBSTITUTE you first have to know the character number you're trying to clean up. It's just as well then that you can use the CODE function to find this out.

Other SUBSTITUTE Formulas

The SUBSTITUTE function isn't limited to working with the CHAR function. You can use it to replace any text.

For example, let's say we wanted to replace the text 'Iron Man' with 'Captain America' in this text:

Cell A1: Iron Man is the greatest Avenger

Your SUBSTITUTE formula would be:

=SUBSTITUTE(A1,"Iron Man","Captain America")

And now it reads:

Cell A1: Captain America is the greatest Avenger

I'm not sure my 4 year old would agree with my changes so it's just as well he can't read yet!

What About TRIM

Now you might think that the TRIM function can replace blank spaces at the end of text, but unfortunately it can’t handle these special characters.

TRIM is only good for removing the standard space character number 32 from text. But don’t forget it ignores single spaces between words.