What does it do?

Removes all nonprintable characters from text

Formula breakdown:


What it means:

=CLEAN(this dirty text cell)

There are times when imported text from other applications contain characters that are unprintable. The CLEAN formula in Excel can clean up the unprintable characters easily.

This is how it is done below (go to the bottom of the page if you want to see the animated gif tutorial):



STEP 1: We need to enter the Clean function



STEP 2: The Clean argument:

Which text do we want to clean the dirty characters from?

This is our data source:

Clean 01

Reference the cell which has the dirty data:


Clean 02


STEP 3: Do the same for the rest of the cells by using the CLEAN formula, notice all of the unprintable characters (Wingdings) have been cleaned:

Clean 03


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Change Phone Area Codes with Excel’s REPLACE Formu... What does it do? Replaces part of a text string, based on the number of characters you specify, with a different text string Formula breakdown: =REPLACE(old_text, start_num, num_chars, new_text) What it means: =REPLACE(this cell, starting from this number, all the ...
Import Data from Text Using Power Query or Get �... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data. But what if your data source is not in your Excel spreadsheet? If it's inside a text file, it's very easy to import data from text and right into Power Query!...
Remove Second Hyphen with Excel’s SUBSTITUTE... What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, By this text character, To this text character, ) There are times where I needed to remove t...
Calculate the Compound Interest with Excel’s... What does it do? Calculates the compound interest Formula breakdown: =FV(rate, nper, pmt, ) What it means: =FV(interest rate, number of periods, periodic payment, initial amount) Say that you turn 18 years today (CONGRATS!) and you find out that your parents...