Excel CLEAN Function
last modified October 5, 2026
The CLEAN function is a text function in Excel that removes the
non-printing control characters of the 7-bit ASCII set (codes 0 through 31)
from text. These characters often come from other applications or systems and
are invisible, yet they can break lookups, sorting, and comparisons. This
tutorial provides a comprehensive guide to using the CLEAN
function with detailed examples. You'll learn its syntax, practical
applications, and how to combine it with other functions.
CLEAN Function Basics
The CLEAN function removes the first 32 non-printing characters
of the 7-bit ASCII character set, that is, the control codes 0 through 31,
from a text string. These characters can cause display issues or problems in
data processing. The function is especially useful when importing data.
| Component | Description |
|---|---|
| Function Name | CLEAN |
| Syntax | =CLEAN(text) |
| Arguments | text - The text from which to remove non-printing characters |
| Return Value | Text with the ASCII control characters 0-31 removed |
This table breaks down the essential components of the CLEAN
function. It shows the function name, basic syntax format, argument
requirements, and return value characteristics.
| Argument | Meaning |
|---|---|
| text | The text to clean. It can be a literal string, a cell reference, or an
expression that returns text, such as A1&B1. Numbers are
coerced to text automatically. |
CLEAN takes exactly one argument. Supplying more than one
argument produces a "too many arguments" error. An ordinary space is a
printable character, so CLEAN leaves every space untouched; use
TRIM to remove those.
Basic CLEAN Example
This example demonstrates the simplest use of the CLEAN function with text containing non-printable characters.
=CLEAN("Hello" & CHAR(7) & "World")
This formula combines "Hello" and "World" with ASCII character 7 (bell) between them. The CLEAN function removes this non-printable character. The result will be "HelloWorld".
CLEAN with Imported Data
When importing data from external systems, non-printable characters often appear. Here's how to clean such data.
| A | B |
|---|---|
| Product ID | |
| P123■45 | =CLEAN(A2) |
| X789▲12 | =CLEAN(A3) |
The table shows imported product IDs containing non-printable characters (represented by ■ and ▲). The CLEAN function in column B removes these characters.
=CLEAN(A2)
This formula cleans the text in cell A2, removing any non-printable characters. For "P123■45", it returns "P12345". This is essential for data normalization.
CLEAN vs TRIM
CLEAN and TRIM solve two different problems and are
often confused. CLEAN removes invisible control characters but
keeps every space. TRIM removes leading and trailing spaces and
collapses runs of internal spaces to a single space, but it does not remove
control characters or the non-breaking space. The following table summarizes
the difference.
| Character | CLEAN | TRIM |
|---|---|---|
| ASCII control codes 0-31 | Removes | Keeps |
| Leading and trailing spaces | Keeps | Removes |
| Runs of internal spaces | Keeps | Collapses to one |
| Non-breaking space CHAR(160) | Keeps | Keeps |
Combining the two functions handles control characters and ordinary spaces in a single formula.
| A | B |
|---|---|
| Data with issues | |
| =TRIM(CLEAN(A1)) |
This table demonstrates cleaning text with both non-printable characters and extra spaces. The combined formula in B2 handles both issues simultaneously.
=TRIM(CLEAN(A1))
This formula first removes non-printable characters with CLEAN, then eliminates extra spaces with TRIM. For " Data with issues ", it returns "Data with issues". This is ideal for data cleaning workflows.
Removing the Non-Breaking Space
A frequent companion of control characters is the non-breaking space,
CHAR(160). It is a printable character, so CLEAN
leaves it in place, and TRIM does not remove it either, because
TRIM only handles the ordinary space, CHAR(32). The
standard recipe therefore adds SUBSTITUTE.
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
The formula first converts every non-breaking space to a normal space with
SUBSTITUTE, removes the control characters with
CLEAN, and finally trims the leftover spaces with
TRIM. The same pattern applies to any character that
CLEAN cannot remove: replace it with SUBSTITUTE
first.
CLEAN with SUBSTITUTE for Advanced Cleaning
For comprehensive text cleaning, CLEAN can be combined with SUBSTITUTE to handle additional problematic characters.
| A | B |
|---|---|
| Text•with•special•chars | |
| =SUBSTITUTE(CLEAN(A1),"•"," ") |
The table shows text with special bullet characters (•) that aren't removed by CLEAN. The formula combines CLEAN with SUBSTITUTE for complete cleaning.
=SUBSTITUTE(CLEAN(A1),"•"," ")
This formula first removes non-printable characters, then replaces bullets with spaces. For "Text•with•special•chars", it returns "Text with special chars". This technique handles characters beyond ASCII 0-31.
CLEAN with Other Text Functions
CLEAN can be part of complex text manipulation formulas combined with functions like LEFT, RIGHT, and MID.
| A | B |
|---|---|
| 123▲XYZ▲456 | |
| =LEFT(CLEAN(A1),3) |
The table demonstrates extracting clean text portions. The formula gets the first 3 characters after cleaning non-printable characters from the source.
=LEFT(CLEAN(A1),3)
This formula cleans "123▲XYZ▲456" to "123XYZ456", then extracts the first 3 characters ("123"). This approach is useful when working with fixed-format data containing control characters.
CLEAN with Numbers and Arrays
The text argument is expected to be text, but Excel coerces a
number to its text form, so =CLEAN(123) returns the text "123". A
range is handled the same way as in other text functions: in Microsoft 365 and
Excel 2021, =CLEAN(A1:A3) spills the cleaned results into three
cells, while older versions require the formula to be confirmed with
Ctrl+Shift+Enter.
=CLEAN(123) ' "123", the number is coerced to text =CLEAN(A1:A3) ' spills three cleaned values in Excel 365
CLEAN Limitations
While CLEAN is powerful, it has a narrow scope. It removes only the 7-bit
ASCII control codes 0 through 31. It does not remove character 127 (DEL) or
the additional non-printing characters that appear in the Unicode and
Windows-1252 character sets, such as 129, 141, 143, 144, and 157. It also
leaves the non-breaking space, CHAR(160), untouched.
| Character | Code | Removed by CLEAN? |
|---|---|---|
| Tab, line feed, carriage return | 9, 10, 13 | Yes |
| Other control codes | 0-31 | Yes |
| Delete | 127 | No |
| Additional non-printing characters | 129, 141, 143, 144, 157 | No |
| Non-breaking space | 160 | No |
=CLEAN(A1)
If A1 contains a character such as CHAR(127) or
CHAR(160), CLEAN returns the text unchanged. To
remove those characters you must target them explicitly, for example with
=SUBSTITUTE(A1,CHAR(160)," "), or use a VBA macro that strips
every code outside the printable range.
CLEAN Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| More than one argument | Too many arguments error | CLEAN accepts a single text argument |
| Argument is an error value | The same error is passed through | CLEAN cannot mask #N/A, #DIV/0!, or similar errors |
| Hidden characters remain after CLEAN | Text still looks or sorts wrong | Remove CHAR(127), CHAR(160), and other Unicode characters with SUBSTITUTE |
| Extra spaces remain after CLEAN | Text has leading, trailing, or double spaces | Wrap the result in TRIM: =TRIM(CLEAN(A1)) |
| A range is passed in an older version | Only the first value is returned or a #VALUE! error appears | Confirm with Ctrl+Shift+Enter, or use Microsoft 365 where it spills |
Most CLEAN problems are not errors at all but characters that lie outside the
0-31 range. When cleaned text still misbehaves, inspect the individual
character codes with CODE or UNICODE and remove the
offending characters explicitly.
The CLEAN function is essential for preparing text data in Excel.
It efficiently removes the ASCII control characters 0-31 that disrupt display,
sorting, and lookups after an import. Its main limitation is that it ignores
character 127, the non-breaking space, and other Unicode non-printing
characters, so the complete cleaning recipe combines it with
TRIM for ordinary spaces and SUBSTITUTE for
characters such as CHAR(160). Mastering these combinations will
significantly improve your data import and text processing workflows.
Author
List all Excel Formulas.