Data Cleaningbeginner
Remove extra spaces from text with TRIM
=TRIM(A2)
Excel how-to guide
How to clean remove nonprintable in Excel
Fix exported CSV or database data that contains line breaks, tab characters, or other invisible characters that prevent VLOOKUP matches or cause display issues.
Step-by-step instructions
How to clean remove nonprintable — 3 steps
1
Reference the cell containing the text with hidden characters.
2
CLEAN removes the first 32 non-printable ASCII characters (character codes 0–31).
3
Combine with TRIM to remove both invisible characters and extra spaces in one step.
Example data
Worked example
| Imported Text | Cleaned Text |
|---|---|
| Product A | Product A |
| Item 002 | Item 002 |
Common mistakes
Errors to watch out for
Cell still looks broken after CLEAN
CLEAN only removes ASCII 0–31. Non-breaking spaces (ASCII 160) and other Unicode control characters require SUBSTITUTE(A2,CHAR(160),"") in addition.
Line breaks remain
Line breaks are CHAR(10) and are removed by CLEAN. If they persist, the character may be CHAR(13) — use SUBSTITUTE(A2,CHAR(13),"") explicitly.
FAQ
Frequently asked questions
Should I use CLEAN and TRIM together?
Yes — =TRIM(CLEAN(A2)) is the standard combination. CLEAN removes invisible characters first, then TRIM removes any resulting extra spaces.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Text Manipulationbeginner
Replace specific text with SUBSTITUTE
=SUBSTITUTE(A2,"-","")