Formula Guide

The Excel CLEAN Function
explained simply

CLEAN removes non-printable characters from text — the kind of invisible junk that often comes in from exported or copy-pasted data.

ExcelPro · 3 min read · Updated June 2026
Contents
  1. What does CLEAN do?
  2. Syntax
  3. 3 examples
  4. CLEAN vs TRIM
  5. FAQ

What does CLEAN do?

CLEAN strips out non-printable characters — things like line breaks and control characters that sometimes get pulled in when copying from a website, PDF, or database export.

It does not remove regular spaces; for that, you need TRIM. The two are often used together on messy imported data.

Syntax

=CLEAN(text)
ArgumentDescription
text requiredThe text to clean, or a cell reference containing it.

Examples

Example 1
Remove invisible line breaks from imported text
=CLEAN(A2)

Strips out line-break characters that sometimes appear when pasting from a website or PDF.

Example 2
Combine with TRIM for thorough cleanup
=TRIM(CLEAN(A2))

Removes both non-printable characters and extra spaces in a single formula.

Example 3
Clean before comparing two cells
=EXACT(CLEAN(A2),CLEAN(B2))

Useful when two values look identical but a hidden character is causing a comparison to fail.

CLEAN vs TRIM

FunctionRemoves
CLEANNon-printable control characters (line breaks, etc.)
TRIMExtra regular spaces (leading, trailing, doubled)

Messy imported data often needs both: =TRIM(CLEAN(A2)) handles invisible control characters and extra spaces in one formula.

Common mistakes

⚠️ Expecting CLEAN to remove regular spaces

It only removes non-printable control characters — use TRIM for ordinary spaces.

FAQ

How do I know if I need CLEAN?
If two cells look identical but a formula like = or EXACT says they are not equal, an invisible character is a likely cause — CLEAN is worth trying.

Practise Excel with real data

ExcelPro has 700+ hands-on Excel exercises across 7 career tracks — free to start, no download needed.

Start practicing free →

Related formulas

TRIM EXACT SUBSTITUTE