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):

Cleaning Data with Excel's CLEAN Formula | MyExcelOnline



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



Get access to 30+ Microsoft Excel & Office courses for ONLY $1.

Click here to get this limited-time $1 offer!


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