Cleaning Data with Excel’s CLEAN Formula

What does it do?

Removes all nonprintable characters from text

Formula breakdown:

=CLEAN(text)

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

Download excel workbookClean.xlsx

STEP 1: We need to enter the Clean function

=CLEAN

STEP 2: The Clean argument:

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

This is our data source:

Reference the cell which has the dirty data:

=CLEAN(C9)

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:

Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.