CLEAN - Remove ASCII control characters
Clean imported tabs and line breaks while understanding why this does not remove ordinary spaces or every invisible Unicode character.
By the end, you can
- Remove characters with codes 0 through 31.
- Distinguish deletion from replacing a separator.
- Combine CLEAN and TRIM only when appropriate.
Before you start: Understand TRIM's ordinary-space rule. CHAR is an Excel function for constructing a character from a code.
In this lesson
An invisible import character can matter
Imported data can contain tabs, carriage returns, and linefeeds that interfere with matching or reporting. Excel CLEAN removes the first 32 ASCII control characters, codes 0–31. It deletes those characters; it does not replace them with word-separating spaces or promise to remove every nonprinting Unicode character.
Syntax and source construction
=CLEAN(text)Supply the imported text. CHAR(9), CHAR(10), and CHAR(13) create a tab, linefeed, and carriage return in Excel.
| Argument | Purpose | Example |
|---|---|---|
textRequired | Text from which ASCII control characters should be deleted. | A2 |
="Anika"&CHAR(9)&" Rao"&CHAR(10)Enter this in A2 to create a tab after Anika and a linefeed after Rao. There is also one ordinary space before Rao.
A contact export with control characters
Anika Rao| A | B | |
|---|---|---|
| 1 | Imported text | CLEAN result |
| 2 | Anika Rao | Anika Rao |
| 3 | Fi nance | Finance |
| 4 | Delhi | Delhi |
=CLEAN(A2)Delete the tab and linefeed. The ordinary space before Rao remains.
- Identify whether the unwanted characters are controls or ordinary/nonbreaking spaces.
- Apply CLEAN in a separate column, preserving the import source.
- Check whether deletion joins words that should remain separated.
- If a tab is a field delimiter, split or replace it before deleting it.
Deletion, spacing, and unchanged normal text
=CLEAN(A3)The tab between Fi and nance is deleted, giving Finance.
=TRIM(CLEAN(A4))CLEAN removes carriage return/linefeed; TRIM then removes the leading ordinary space.
=CLEAN("Sales Ops")The ordinary space is not an ASCII control character.
Limits and compatibility
- CLEAN removes codes 0–31, including tab 9, linefeed 10, and carriage return 13.
- It does not by itself remove ordinary space 32, nonbreaking space 160, or additional nonprinting codes such as 127, 129, 141, 143, 144, and 157.
- Deleting a separator can merge fields: Sales followed immediately by a tab and Ops becomes SalesOps. A targeted SUBSTITUTE or split may be preferable.
- CLEAN and CHAR are available in older and current Excel. This cleaning rule is not a general Unicode normalization or character-validity test.
Common mistakes and where to run these examples
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.