CLEAN function: strip line breaks and control characters without fusing words

What CLEAN removes—and the word-joining trap

CSV exports from CRM and ticketing tools often embed line feeds (CHAR(10)) or carriage returns (CHAR(13)) inside street addresses and notes. CLEAN(text) strips ASCII control codes 0 through 31 in one pass.

However, CLEAN deletes CHAR(10) without inserting a space. Calling CLEAN directly on "Suite 400" & CHAR(10) & "North Wing" produces "Suite 400North Wing". Replacing CHAR(10) with a space before running CLEAN and TRIM keeps words separated cleanly.

Safe single-line cleanup pattern

SUBSTITUTE turns line breaks into spaces first, CLEAN removes any remaining tabs (CHAR(9)) or carriage returns (CHAR(13)), and TRIM collapses consecutive spaces into one.

This pattern preserves separation at line feeds only. CLEAN deletes tabs and carriage returns without adding spaces; replace those separators with spaces as well if they separate words in your source.

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

Why non-breaking spaces (CHAR(160)) survive CLEAN

Web-scraped tables frequently contain Unicode/ANSI non-breaking spaces (code 160). Because 160 lies outside ASCII 0-31, neither CLEAN nor TRIM removes it unless you substitute CHAR(160) explicitly.

=LEN(CLEAN(A3))

Further reading

• Microsoft Support — CLEAN function