(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
- leading, trailing or embedded space characters (Unicode character set values 32 and 160), and
- 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)
Trim() removes non-printing character 32.
For higher value characters, use substitute(). As an example, to remove character 160, use