Cellwise Start free

Text function

Excel CLEAN function

CLEAN removes the non-printing control characters, codes 0 to 31, from text. In practice that means line breaks (code 10), carriage returns (13) and tabs (9) that arrive in pasted or exported data. It does not touch spaces, including the non-breaking space (160), so it is usually combined with TRIM and SUBSTITUTE.

=CLEAN(text)

Arguments

text
The text or cell to clean.

CLEAN examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

A
1Notes (exported)
2Call back after 3pm
3Tab separated
4Acme Ltd 
5 Order #4471

Download this sheet as an Excel practice file (.xlsx)

  1. =CLEAN(A2)

    Result: Call backafter 3pm

    The line break is gone, but so is the gap: Call backafter 3pm. CLEAN deletes the character, it does not replace it with a space.

  2. =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10)," ")))

    Result: Call back after 3pm

    Swap line breaks for spaces first, then CLEAN anything else and TRIM the extra spaces: Call back after 3pm.

  3. =LEN(A5)-LEN(CLEAN(A5))

    Result: 1

    A quick test: one control character was removed from A5 (the line break at the end).

  4. =TRIM(CLEAN(A5))

    Result: Order #4471

    The classic clean-up pair for imported text: CLEAN removes the line break, TRIM removes the extra spaces, leaving Order #4471.

Reference: Non-breaking spaceLine breaks in cells

How to use CLEAN

  1. Add a helper column next to the messy text.
  2. Type =TRIM(CLEAN(A2)) and fill it down.
  3. If words run together, use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10)," "))) instead.
  4. Copy the helper column and use Paste Special > Values over the original if you want to keep only the clean text.

Common CLEAN mistakes

Words glued together

CLEAN deletes a line break with no space in its place. Replace CHAR(10) with a space using SUBSTITUTE before you CLEAN.

Expecting CLEAN to fix non-breaking spaces

Code 160 is not a control character, so CLEAN keeps it, and TRIM does too. Use SUBSTITUTE(A2,CHAR(160)," ") first.

Cleaning numbers

CLEAN always returns text. If the cell held a number with a stray line break, wrap the result in VALUE or multiply by 1 so SUM sees it as a number.

CLEAN FAQ

How do I remove line breaks in Excel?

Use =SUBSTITUTE(A2,CHAR(10)," ") to turn them into spaces, or =CLEAN(A2) to delete them. Find and Replace also works: press Ctrl+J in the Find box to search for a line break.

What does CLEAN not remove?

It only removes codes 0 to 31. Spaces, the non-breaking space (160) and the Unicode control characters 127, 129, 141, 143, 144 and 157 stay.

What is the difference between TRIM and CLEAN?

TRIM removes extra normal spaces. CLEAN removes non-printing characters such as line breaks and tabs. Messy imports usually need both: =TRIM(CLEAN(A2)).

Related functions

Updated