The CLEAN function
Line breaks and invisible characters pasted from a web page or software: you cannot see them, but a lookup no longer finds anything.
Syntax
=CLEAN(text)
In a French Excel: =EPURAGE(texte).
Arguments
- text: the cell.
An example
=TRIM(CLEAN(A2))
Removes invisible characters, then extra spaces.
Pitfalls
- The non-breaking space (CHAR(160)) is not removed: SUBSTITUTE first.
- Some invisible Unicode characters (zero-width space) slip through.
Going further
- TRIM, SUBSTITUTE.
- Clean text: every invisible character, Unicode included, across a whole file.
Frequently asked questions
- How do I remove line breaks from a cell?
=CLEAN(A2), or=SUBSTITUTE(A2,CHAR(10)," ")to replace them with a space.