CLEAN Function – Explained with Examples

CLEAN is used to remove any of the non-printing control characters – codes 0-31 – in ASCII text (which are by design also the same values in Unicode).

Function Syntax

=CLEAN(text)
  • text (or string)

Usage

In ASCII codes, the range 32 (the space character) to 126, contain the most common text characters and printable symbols.

The first set of characters, 0-31 inclusive, contained control characters used to control printers and print setting.

ANSI ASCII Extended character set (codes 0-127 only) returned by the Excel CHAR function
ASCII, rendered in Excel with CHAR function

NOTE: Excel renders some control characters as squares or rectangle symbols. Other non-printing characters have no visible output.

Examples

CLEAN is used to remove any of the control characters in the ASCII range 0-31. By their nature control characters, which CLEAN addresses, are non printing and so cannot be shown in a visual way, however we can see the effect by counting the characters before and after

We can create text with a control character in (with values 0-31), in this case we will inject the CHAR(10) control character in a text string below

="hello" & CHAR(10) & "world"

Applying the CLEAN function to our string would look like this

=CLEAN("hello" & CHAR(10) & "world")

would result in the equivalent of

="hello" & "world"

or visibly (note we did not include a space in our original text)

helloworld

Another way to see the effect of CLEAN is by using a text editor to prepare our initial text and count the number of characters in the text before and after using the CLEAN function.

Using Notepad++‘s Show All Characters option we can set up our example with various distributions of spaces in text as well as an example with a control character, Tab, to see what happens.

TIP: Notepad++ is a very useful tool for inspecting data you have especially to check for the presence of errant characters.

Example data containing leading, trailing and duplicate spaces, and a Tab character. (The CR, LF characters relate to line separation)

The Tab character (ASCII char 09 – Horizontal Tab to give it its official name) is removed and the character count is reduced by one character.

After the CLEAN function had been applied, the Horizontal Tab has been removed, as indicated by the decrease in the length of the text. The output also shows that spaces (code 32) and other printable characters (greater than 31) are unaffected by the CLEAN function

Notepad++ showing output of Excel (note the CR and LF – Carriage Return & Line Feed – characters are not part of the original text but are the result of pasting cell data into Notepad from Excel)

Related Topics

F008