Quick Navigation
CLEAN Function
Summary
The CLEAN function removes all nonprintable characters from text strings, making imported data clean and printable.
Syntax
CLEAN(text)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| text | Text |
Yes | The text string from which nonprintable characters will be removed. |
Using the CLEAN Function
Use CLEAN to strip nonprintable characters like line breaks, tabs, and control codes from text imported from other applications or databases.
Common CLEAN Examples
Remove Tab and Line Break
=CLEAN(CHAR(9)&"Monthly report"&CHAR(10))Removes tab (CHAR(9)) and line break (CHAR(10)) characters, returning clean text 'Monthly report'.
Frequently Asked Questions
No, CLEAN only removes the first 32 ASCII nonprintable characters (0-31). Additional Unicode nonprintable characters require other methods.
CLEAN works on text representations. Numbers are automatically converted to text when passed to CLEAN.
Common Errors and Solutions
#VALUE!
Cause: Invalid argument type passed to text parameter.
Solution: Ensure the text parameter contains valid text or reference.
Notes
- Does not affect printable characters
- Combine with SUBSTITUTE for comprehensive cleaning
- Useful before CONCATENATE or TEXTJOIN functions
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365, Excel Online
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+