CLEAN function
Quick answer
CLEAN removes non-printing characters, such as line breaks and tabs, from text.
- Syntax
=CLEAN(text)- Most common use
=CLEAN(A2)returns Maya Chen (The trailing line break is gone)
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.
Answers that use CLEAN
In other languages
CLEAN is LIMPIAR in Spanish Excel and SÄUBERN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .