How to remove non-printing characters in Excel
Formula (Excel and Google Sheets)
=TRIM(CLEAN(A2))Use =TRIM(CLEAN(A2)). CLEAN removes the invisible control characters (codes 0 to 31) and TRIM removes extra spaces.
- Result on the example
- one result per row in
B2, filled down toB6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.
How it works
Exports from databases and other systems can carry tabs, carriage returns and other control characters you can't see. They make "INV-1042" and "INV-1042" plus a tab different values, so lookups fail. CLEAN strips characters 0 to 31, and TRIM tidies the spaces that remain.
In Google Sheets
The same formula works in Google Sheets: =TRIM(CLEAN(A2)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =CLEAN(A2) | INV-1042, Contoso Ltd, Northwind Traders… | CLEAN alone, when you know there are no extra spaces. |
Common mistakes
- CLEAN doesn't remove the non-breaking space (character 160) or Unicode characters like the zero-width space. Use SUBSTITUTE for those.
- CLEAN removes line breaks too, joining the words on either side. Replace CHAR(10) with a space first if the lines should stay apart.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Remove non-printing characters
B2, remove the invisible control characters and extra spaces from the imported text, and fill down to B6.Questions
How do I find out which invisible character is there?
Check its code: =CODE(RIGHT(A2)) for the last character, or =CODE(MID(A2,5,1)) for the fifth.
How do I count the hidden characters?
Compare lengths: =LEN(A2)-LEN(CLEAN(A2)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.