03 November 2015

How to clean non-printing characters in Excel

(This is more a convenient note to myself that anything else.)

Text String Cleaning (source: Microsoft Support)

Offending bits of text that can mess up VLOOKUP or INDEX(MATCH) are typically
  1. leading, trailing or embedded space characters (Unicode character set values 32 and 160), and 
  2. non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157) 
Assuming the pesky malformed text string is in cell A1.

Clean() removes the first 32 non-printing characters (0 to 31)
=CLEAN(A1)

Trim() removes non-printing character 32.
=TRIM(A1)

For higher value characters, use substitute(). As an example, to remove character 160, use
=SUBSTITUTE(A1,CHAR(160),"")

That's it for now!