Microsoft Office Tutorials and References
In Depth Information
Using CLEAN to Remove Nonprintable Characters from Text
Figure 11.54. TRIM
TRIM removes leading spaces and extra interior spaces.
removes leading spaces and extra interior spaces.
Using =LEN(C1) shows that the text actually contains 15 characters in-
stead of six characters. The TRIM(C1) formula removes any leading spaces,
any trailing spaces, and any extra interior spaces. The function still
leaves one space between ABC and DEF because you want to continue to have
words separated by a single space.
The formulas in cells C5 and C6 confirm that the leading and trailing spaces
are removed and that the length of the new value is only seven characters.
CLEAN to Remove Nonprintable Characters from Text
to Remove Nonprintable Characters from Text
Although TRIM works great, the CLEAN function no longer works as advert-
ised. CLEAN is designed to remove nonprintable characters from text.
Besides extra spaces, another annoying problem with data from other sys-
tems is that it might contain nonprintable characters. Excel offers a func-
tion that is supposed to remove nonprintable characters, but Microsoft ’ s
definition of a nonprintable character is far too narrow. The function was
clearly written before the proliferation of web queries, Oracle, and SAP.