Text & Cleanup

Excel formula for Remove non-printable characters

Strip hidden line breaks and control characters from imported text.

Copy this formula

=CLEAN(TRIM(A2))

How the formula works

CLEAN removes non-printable control characters (like stray line breaks from a paste), and TRIM tidies up the spaces. It's the go-to cleanup when imported data looks fine but won't match or align.

Examples

=CLEAN(TRIM(A2))→clean text

Removes hidden characters and extra spaces.

Other ways to do this

Also strip CHAR(160)

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Handles non-breaking spaces from the web.

FAQ

CLEAN didn't remove the character — why?

CLEAN only removes codes 0–31. For others like CHAR(160), use SUBSTITUTE to target them.

Related formulas

Remove extra spaces from text Convert text to a number

Functions used

CLEAN TRIM SUBSTITUTE

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free