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

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

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

Share on Google+

Google+

Related Posts

Getting the remainder with Excel’s MOD Formu... What does it do? Gives you the remainder after dividing one number with another Formula breakdown: =MOD(number1, number2) What it means: =MOD(a number, divided by this number) In Excel it is very easy to divide two numbers. But how about if you need to ge...
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...
CountIf Formula in Excel What does it do? Counts the number of cells that matches your specified condition Formula breakdown: =COUNTIF(range, criteria) What it means: =COUNTIF(range of cells to check, condition to check against) Do you have a scenario where you want to count the num...
Extracting Data with Excel’s RIGHT Formula What does it do? It returns the last character or characters in a text string, based on the number of characters you specify. Formula breakdown: =RIGHT(text, ) What it means: =RIGHT(look in this cell, extract X characters) There are times when you will need ...