CLEAN Function
Strips non-printable control characters from text.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| text | The text or cell reference from which to remove non-printable characters. | Required |
Basic Example
Remove a non-printable prefix
=CLEAN(CHAR(9) & "Data")CHAR(9) is a tab (a non-printable control character) that CLEAN strips, leaving "Data".
Advanced Examples
Example 1: Clean imported data
Cell A1 has hidden line-feed characters from a CSV exportStrip control codes before matching
=CLEAN(A1)Example 2: Combine with TRIM
Data has both control chars and extra spacesRemove non-printables and normalize spaces
=TRIM(CLEAN(A1))How CLEAN Works
CLEAN scans the string and deletes characters whose code points fall in the 0-31 control range. It does not remove printable characters or, by default, characters above 31.
Important Notes & Limitations
Only removes characters 0-31; it does not strip all Unicode control or format characters.
Excel 2016+ adds an optional second argument for cleaning additional character sets, but the classic form uses one argument.
Does not remove printable junk like extra spaces (use TRIM for that).
Common Errors & Fixes
Still mismatched lookupsCLEAN removed control chars but extra spaces remain.Fix: Wrap the result in TRIM as well: =TRIM(CLEAN(cell)).
Download Practice File
CLEAN
cleanPractice CLEAN with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the CLEAN function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps