CLEAN Function in Excel: Remove Non-Printing Characters

📎 This article includes 1 downloadable practice file ↓

TextLevel: IntermediateAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Common errors and fixes
6. CLEAN in practice
7. Where you will use it
8. Worked example
9. Mistakes people make
10. Related functions
11. Continue learning

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),” “).

CLEAN in practice

Where you will use it

• Remove line breaks from data pasted from PDFs or ERP
• Fix lookups failing due to hidden characters
• Tidy text before splitting

Worked example

Raw text	Formula	Result
Invoice 1045	=CLEAN(A2)	Invoice1045
GST IN	=CLEAN(A3)	GSTIN
Plain	=CLEAN(A4)	Plain

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Expecting it to remove non-breaking spaces	CLEAN removes codes 0-31 only; use SUBSTITUTE(A2,CHAR(160)," ")
Not trimming afterwards	Use TRIM(CLEAN(A2)) to also tidy spaces

Related functions

TRIM · SUBSTITUTE · CHAR

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

Continue learning

All Text guides →

REGEXREPLACE Function in Excel: Find and Replace With PatternsREGEXREPLACE replaces text matching a pattern — strip every non-digit, collapse repeated spaces, mask…T Function in Excel: Keep Text, Blank Out NumbersT returns text unchanged and anything else as an empty string. Rarely needed alone,…NUMBERVALUE Function in Excel: Convert Text Numbers With Any SeparatorsNUMBERVALUE converts text to a number using separators you specify — essential for European-style…VALUE Function in Excel: Convert Text to a NumberVALUE turns text that looks like a number into a real number, so SUM…

📎 Practice files for this article

• 📗
  Practice workbook: Text & cleaning24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.