How to Use the CLEAN Function in Google Sheets
=CLEAN(text). It removes only non-printable ASCII characters.
What CLEAN does
Text pasted from a web page, a PDF or an old system sometimes carries invisible control characters. They cannot be seen, but they make a lookup fail or a length come out wrong. CLEAN removes them. It is often used together with TRIM, which removes extra spaces.
CLEAN syntax
=CLEAN(text)
Per Google's help page, text is the text whose non-printable characters you want removed. Two points matter:
- The function removes only non-printable ASCII characters. Non-printable Unicode characters that are not found in ASCII are not removed.
- Sheets does not display non-printable characters, so cleaning text usually produces no visible change. A good check is LEN: the length drops when something was removed.
A step-by-step example
Cell A2 holds text copied from a web page that may contain hidden characters.
| A | |
|---|---|
| 1 | Imported text |
| 2 | Total 120 |
=CLEAN(A2)
This returns the same text with any non-printable ASCII characters removed. The help page's own example builds such a character on purpose:
=CLEAN("AF" & CHAR(31))
This returns AF. CHAR(31) adds a control character after AF, and CLEAN removes it.
Common mistakes
- Expecting a visible change. Compare LEN before and after.
- Expecting it to fix spaces. Use TRIM for extra spaces.
- Expecting it to remove every odd character. Unicode characters outside ASCII stay.
Try it yourself
Use the cell above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Clean a cell
Write a formula that removes the non-printable characters from the text in A2.
Exercise 2: Remove CHAR(31)
Write a formula that cleans the text "AF" followed by CHAR(31), joined with the ampersand.
What to learn next
- REPT: repeat text a number of times.
- TRIM: remove extra spaces from text.
- SUBSTITUTE: replace text inside a string.
Syntax and rules in this guide were checked against Google's CLEAN help page. See all guides.