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 WORKBOOK

 

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:

Clean 01

Reference the cell which has the dirty data:

=CLEAN(C9)

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

Clean

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

BETWEEN Formula in Excel There is no explicit Between formula in Excel, however we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10?We have three possible scenarios: numbers, dates, and text. ...
Cleaning Data with Excel’s PROPER Formula  What does it do?Capitalizes the first letter in a text string and any other letters in the text that follow a space. Converts all other letters to lowercase letters.Formula breakdown:=PROPER(text)What it means:=PROPER(this text cell)There are...
Round Off Numbers with Excel’s ROUND Formula What does it do?Rounds a number to the nearest decimal based on your specified number of digitsFormula breakdown:=ROUND(number, num_digits)What it means:=ROUND(the number, number of decimal places to round off)Ever had the need to round off numbers? ...
Count Text Occurrences with Excel’s SUBSTITU... What does it do?It replaces specific text in a text string i.e. Substitutes new text for old text in a text string.Formula breakdown:=SUBSTITUTE(text, old_text, new_text, )What it means:=SUBSTITUTE(This cell, From this TEXT character, To this TEXT character, )...