CLEAN Function in Excel: Remove Non-Printing Characters

⏱ 1 min readUpdated 28 September 2026

TextLevel: IntermediateAvailable in: Excel 2007+

In this article
  1. Syntax
  2. Examples
  3. Example 1
  4. Example 2
  5. Common errors and fixes
  6. Related functions

CLEAN removes invisible control characters (like line breaks from system exports) that break lookups and look like spaces.

Syntax

=CLEAN(text)
Argument What it means
text Text to clean.

Examples

Example 1

=TRIM(CLEAN(A2))

The standard clean-up for pasted data.

Example 2

=SUBSTITUTE(A2, CHAR(10), ", ")

Keep the content but turn line breaks into commas.

Common errors and fixes

You see Why, and the fix
Still does not match Non-breaking spaces (CHAR 160) are printable — replace them with SUBSTITUTE(A2,CHAR(160),” “).

TRIM · SUBSTITUTE · CHAR

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Keep reading

Excel Functions A–ZMIN Function in Excel: Smallest Value in a RangeExcel Functions A–ZLEN Function in Excel: Count Characters in TextExcel Functions A–ZLOWER Function in Excel: Convert Text to Small Letters
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong