Text
CLEAN
Definition
CLEAN returns text with non-printing characters removed.
Syntax
=CLEAN(text)
Arguments
| Argument | Meaning |
|---|---|
text |
The text, or the cell holding it, to strip of non-printing characters. |
Worked example
A paste from another system prefixes a tab character before each desk label. The labels are built in A2:A4.
| A: Raw label |
|---|
| =CHAR(9)&"desk" |
| =CHAR(9)&"lamp" |
| =CHAR(9)&"mat" |
Formula: =CLEAN(CHAR(9)&"desk")
Result: desk. CHAR(9) is a tab, which CLEAN removes, leaving the printable letters.
Practice
Cell A2 holds the formula =CHAR(7)&"ok", which prefixes a non-printing character. In B2, write a formula that removes that character and leaves ok.
- In B2, write a CLEAN formula that removes non-printing characters from A2.
- B2 should show A2 without non-printing characters.
Related functions
TRIM removes extra spaces from printable text. SUBSTITUTE can replace a leftover character that CLEAN does not strip. CLEAN is the step that drops non-printing characters.
Common mistake
Expecting CLEAN to remove a non-breaking space: CHAR(160) stays in the text, so you still need SUBSTITUTE for that character.