What is the CLEAN Function in Excel?
What is the CLEAN Function in Excel?
The CLEAN function in Excel is used to remove non-printable characters from text.
Sometimes when you copy data from the web, databases, or other applications into Excel, it brings along “hidden” characters—things like line breaks, control characters, or symbols that you can’t see but that mess up your data.
That’s where CLEAN comes in—it acts like a broom, sweeping away those invisible characters so your text is neat and usable.
Syntax
=CLEAN(text)
-
text = the cell or text string you want to clean.
Example
Let’s say you copied this messy text into Excel:
Cell A1: Hello ▯World
(That little square ▯ is a non-printable character.)
Now, if you use:
=CLEAN(A1)
The result will be:
Hello World
The unwanted character disappears!
Common Uses of CLEAN
-
Imported Data – Cleaning up messy data copied from websites or databases.
-
Removing Line Breaks – Text copied from PDFs or Word often has hidden line breaks that CLEAN can fix.
-
Combining with TRIM – If you also want to remove extra spaces, use:
=TRIM(CLEAN(A1))This removes both unwanted characters and extra spaces.
Limitations
-
CLEAN only removes the first 32 non-printable ASCII characters (0 to 31).
-
It does not remove all spaces (e.g., non-breaking spaces from web pages). For that, you might need SUBSTITUTE.
Example:
=TRIM(SUBSTITUTE(CLEAN(A1),CHAR(160),""))
This combination handles stubborn non-breaking spaces too.
In short: The CLEAN function is your go-to tool for tidying up hidden characters in Excel text. It’s especially handy when working with imported or copy-pasted data.
Comments
Post a Comment