Pinterest Pixel

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... read more

Download Excel Workbook
Bryan
Posted on

Overview

 

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

Cleaning Data with Excel's CLEAN Formula | MyExcelOnline
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:

Cleaning Data with Excel's CLEAN Formula

Reference the cell which has the dirty data:

=CLEAN(C9)

Cleaning Data with Excel's CLEAN Formula

 

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:

Cleaning Data with Excel's CLEAN Formula

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Cleaning Data with Excel's CLEAN Formula | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!