CLEAN Hasn’t Kept Up With The Times

December 30, 2021 - by Bill Jelen

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.

Bill Alt+Enter Jelen is CLEANED to BillJelen. Instead, you could use =SUBSTITUTE(A2,CHAR(10)," ") to replace the Alt+Enter with a space. In another example, TRIM is not removing spaces. You learn that what looks like a space is not character 32 but actually character 160 using =CODE(MID(A3,5,1)). To convert the Character 160 back to Character 32, use =TRIM(SUBSTITUTE(A3,CHAR(160))," ")) — Figure 244. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by The Creative Exchange on Unsplash