CLEAN function
CLEAN removes non-printing characters, such as line breaks and tabs, from text. Use it on data pasted from other systems, where hidden characters make cells look identical but fail to match, or push text onto a second line.
- Available in
- Every current version
- Category
- Text functions
- Returns
- Text with the non-printing characters (codes 0 to 31) removed.
Syntax
=CLEAN(text)
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text or cell to clean. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =CLEAN(A2) | Maya Chen | The trailing line break is gone |
| D3 | =LEN(C3)-LEN(CLEAN(C3)) | 1 | One hidden character found in the code |
| D4 | =CLEAN(B4) | 5 Elm RdUnit B | Removing a line break joins the words; use SUBSTITUTE if you need a separator |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Imported name | Address | Code | Result |
| Maya Chen | 12 Oak St Apt 4 | WDG-01 | Maya Chen |
| Raj Patel | 88 Pine Ave | GDT-02 | 1 |
| Lee Wong | 5 Elm Rd Unit B | GZM-03 | 5 Elm RdUnit B |
Practice CLEAN
Graded instantly, then re-checked on changed data.
Strip hidden characters
B2, remove the non-printing characters and the extra spaces. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Nonbreaking spaces remain | CLEAN only removes codes 0 to 31. The nonbreaking space (160) survives; replace it with SUBSTITUTE(A2,CHAR(160)," "). |
| Words run together | Deleting a line break doesn't add a space, so "Rd" and "Unit B" become "RdUnit B". Swap the break for a space instead: =SUBSTITUTE(A2,CHAR(10)," "). |
Tips
- Pair it with TRIM: =TRIM(CLEAN(A2)) handles most messy imports in one step.
Questions
What does CLEAN do in Excel?
It strips non-printing characters (codes 0 to 31), such as line breaks, tabs and carriage returns, from text.
How do I remove line breaks in Excel?
Use =CLEAN(A2) to delete them, or =SUBSTITUTE(A2,CHAR(10)," ") to turn each break into a space so words don't run together.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.