Excel CLEAN Function

CLEAN removes non-printable characters from a piece of text, returning a version of the string with those characters gone. It only targets the first 32 characters of the 7-bit ASCII set (values 0 through 31), things like line feeds and carriage returns that don't display as symbols but still mess up your data. It does not remove regular spaces, and it won't touch a non-breaking space either. If your imported data looks fine but still behaves oddly, that's usually why.

Available in: Excel 2000 and later, including Excel 365. CLEAN behaves identically across every version.

Syntax

=CLEAN(text)

Parameter	Required	Description
text	Yes	The text you want to strip of non-printable characters. Can be a literal string, a cell reference, or a formula that returns text.

CLEAN takes exactly one argument. There's no optional switch to configure.

Basic Example

You've exported product notes from an old inventory system, and column A now contains entries with embedded line breaks that break your reports:

=CLEAN(A2)

// A2 = "Fragile item\nHandle with care"

CLEAN scans the string in A2, finds the line feed character (CHAR 10) sitting between "item" and "Handle," and deletes it. The result reads as one continuous line: "Fragile itemHandle with care." Notice there's no space where the line break used to be. That's the detail that trips up most beginners, and it's exactly why CLEAN almost always gets paired with TRIM.

How CLEAN Works

Which characters CLEAN actually removes

CLEAN only removes ASCII values 0 through 31. That range covers line feed (10), carriage return (13), tab (9), and a handful of other control characters left over from early computing systems. Anything numbered 32 or higher passes through untouched, no matter how invisible it looks on screen.

What CLEAN leaves behind

CLEAN will not remove extra space characters. A cell containing "Chicago IL" with three spaces between words stays exactly as messy after CLEAN as it was before. Non-breaking spaces are the classic offender here: CHAR(160) sits outside the 0-31 range, so CLEAN ignores it completely even though it looks identical to a normal space.

CLEAN deletes line breaks whether you want them or not

If a cell has an intentional line break, say, a multi-line address or a note field where you deliberately pressed Alt+Enter, CLEAN removes it along with everything else in its target range. There's no way to tell CLEAN "keep this one." If preserving line breaks matters, don't run CLEAN on that column.

CLEAN works on numbers and formulas too, not just typed text

You can point CLEAN at a cell containing a number or a formula result, and Excel converts it to text automatically before applying the cleanup. =CLEAN(123) simply returns "123" as text, since there's nothing in the 0-31 range to strip. It's a no-op in that case, not an error.

Common Use Cases

Cleaning text copied from a website or PDF

Data pasted from web pages or PDF exports often carries invisible line breaks that don't show up until you try to sort or filter the column.

=CLEAN(B2)   // removes embedded line feeds from pasted web text

Building a full clean with TRIM

Most real-world imports have both non-printable characters and extra spaces at the same time. Stack the two functions so TRIM cleans up whatever CLEAN leaves behind:

=TRIM(CLEAN(C2))

// CLEAN(C2) strips line breaks and control characters first
// TRIM then collapses any leftover double spaces and trims leading/trailing spaces

Run CLEAN first, then wrap the result in TRIM. Reversing the order doesn't cause an error, but it's the wrong sequence conceptually since TRIM won't touch the control characters CLEAN is built to remove.

Preparing text before joining or exporting

Concatenating messy fields with TEXTJOIN often produces garbled output if the source cells contain hidden line breaks.

=TEXTJOIN(", ", TRUE, TRIM(CLEAN(D2)), TRIM(CLEAN(E2)))

Cleaning each field before the join keeps the final string on one line instead of wrapping mid-sentence.

Preparing text before exact-match lookups

A lookup column with hidden line breaks or control characters can make VLOOKUP or an exact-match formula fail even when two cells look identical on screen.

=VLOOKUP(TRIM(CLEAN(A2)), lookup_table, 2, FALSE)

Cleaning the lookup value first removes the invisible mismatch before the comparison ever runs.

Handling Errors

CLEAN itself rarely throws an error when given normal text or numbers. Where it does fail is when the source cell already contains an error value, since CLEAN can't clean something that isn't valid text to begin with. In that case CLEAN just passes the error straight through.

Common causes of a propagated error:

• The referenced cell contains #N/A, #REF!, or another error from an upstream formula
• The cell reference points outside the range you meant to clean, returning #REF!
• A linked workbook or external source has broken, returning an error instead of text
• CLEAN is nested inside a VLOOKUP or INDEX/MATCH formula that returned an error before CLEAN ever got a chance to run

=IFERROR(CLEAN(F2), "Check source cell")

Notes & Gotchas

What is the CLEAN function in Excel and what does it do?

CLEAN removes non-printable control characters, specifically ASCII values 0 through 31, from a text string. It's designed for text that arrives from another system: a database export, a mainframe report, a PDF copy-paste. It doesn't touch spacing, formatting, or any character outside that narrow range.

How do you use the CLEAN function in Excel?

The syntax is =CLEAN(text), where text is a cell reference or string. Type =CLEAN(A2) in an empty cell, press Enter, and Excel returns A2's contents with any embedded line breaks or control characters stripped out. There's only one argument, so there's nothing else to configure.

What is the difference between CLEAN and TRIM?

CLEAN removes non-printable control characters. TRIM removes extra spaces, including leading, trailing, and repeated internal spaces, but leaves control characters alone. Neither function fully cleans messy imported data on its own; that's why they're almost always used together as =TRIM(CLEAN(A2)).

Why doesn't CLEAN remove non-breaking spaces or other hidden characters?

CLEAN's range is fixed at ASCII 0 through 31. A non-breaking space is CHAR(160), well outside that range, so CLEAN skips it entirely even though it renders identically to a normal space on screen. If TRIM and CLEAN both fail to fix a cell, a non-breaking space is the most likely culprit.

What if a character is still causing problems after TRIM and CLEAN?

Some non-printable characters fall outside both functions' reach, values like 127, 129, 141, 143, 144, or 157 in the extended character set. Use =CODE(MID(A2,n,1)) on individual characters to find the exact numeric value of the culprit, then remove it with =SUBSTITUTE(A2, CHAR(value), ""). This takes more manual digging than CLEAN or TRIM alone, but it's the only reliable fix for characters neither function was built to catch.

Does CLEAN preserve intentional line breaks in multi-line cells?

No. CLEAN removes every line feed and carriage return it finds, whether you added it on purpose or not. If a column contains addresses or notes where line breaks matter, run CLEAN on a copy of the data instead of overwriting the original.

Related Functions

Function	Use this when...
TRIM	You need to remove extra spaces, not non-printable characters. Pair it with CLEAN for a full cleanup.
SUBSTITUTE	You need to remove a specific character CLEAN can't reach, like a non-breaking space or an extended Unicode control character.
TEXTJOIN	You're combining several cleaned fields into one string with a delimiter.