Problem: The Excel function CLEAN is supposed to get rid of non-printable characters. It doesn't seem to get them all. And sometimes I would rather replace the non-printable character with a space.
Strategy: CLEAN and TRIM were written a long time ago. The CLEAN function was written in the days when the only characters were 0 through 127. It is designed to remove characters 0 through 31 from data, but it does not touch the new nonprintable characters such as 129, 141, 143, 144, and 157.
The TRIM function removes leading spaces, trailing spaces, and repeated internal spaces. However, it was designed before the advent of the web. TRIM works fine with character 32, but ignores the common character 160 that many web pages use.
You will have better results if you identify the code of the offending character and use =SUBSTITUTE.
- Examples of SUBSTITUTE.
In B1 above, CLEAN does successfully remove the Alt+Enter, but it would look better if there were a space instead. The formula shown in C2 solves the problem by replacing CHAR(10) with a space.
In C4, TRIM is not getting rid of the extra interior spaces. The formula shown in C3 uses CODE to identify that those spaces are character 160 instead of regular spaces. The formula shown in C5 uses SUBSTITUTE to replace the CHAR(160) with a regular space.