Remove non-printing characters from a text string.

CLEAN(text)

---

Remove non-printing characters from a text string using either defined values (e.g. "string") or cell references (e.g. C3).

CLEAN syntax requires just the defined value or cell reference to be trimmed.

---

CLEAN with a text string.

In the example below, the CLEAN function is used to remove non-printing characters from a text string. The SUBSTITUTE function is also used to change the wording in the sentence.

---

Syntax

The CLEAN function consists of 1 argument:

1. text. Required. The string from which to clean the non-printing characters.

---

Try it now!

Enter your formula into the grey cells.

---

Scope

CLEAN() can be applied to cell references, text or numbers.

Cell reference:	=CLEAN(C3) || returns the contents of cell C3 without any non-printing characters.
Text:	=CLEAN(" South America "&CHAR(10)) || returns " South America " (without the line break but with the additional spaces. Use the TRIM function to remove the additional spaces, for example =TRIM(CLEAN(" South America "&char(10))).

Caveats

• CLEAN removes the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. This includes line breaks.
• CLEAN does NOT remove nonprinting characters (values 127, 129, 141, 143, 144, and 157) from the Unicode character set. Use the SUBSTITUTE function for that purpose.
• CLEAN does NOT remove additional spaces. Use the TRIM function for that purpose.

---

Related Functions

• SUBSTITUTE to clean up nonprinting characters (values 127, 129, 141, 143, 144, and 157) from the Unicode character set.
• TRIM to remove additional spaces.

---

Functions Category

• Text