CLEAN Function

Removes non-printable characters from the given text

What is the CLEAN Function?

The CLEAN function is categorized under the Text function. The function removes non-printable characters from the given text. As financial analysts, we would often import data from various sources and the CLEAN function would help remove nonprintable characters from THE supplied text string. It is also useful in removing line breaks.

The function was introduced to remove the non-printable characters represented by numbers 0 to 31 in 7-bit ASCII code, which are often found at the start of data imported into Excel from other applications.

Formula

=CLEAN(text)

The CLEAN function includes the following argument:

1. Text (required argument) –  The worksheet information from which we intend to remove non-printable characters.

Remember that the CLEAN function differs from the TRIM function, as the former eliminates non-printable characters, represented by ASCII numeric codes 0 to 31. On the other hand, the TRIM function helps get rid of additional spaces that are represented by the ASCII numeric code 32.

How to use the CLEAN function in Excel?

The CLEAN function is a built-in function and can be used as a worksheet function in Excel. As a worksheet function, it can be entered as part of a formula in a cell of a worksheet.

To understand the uses of the function, let’s consider a few examples:

Example 1

Suppose we imported some data in the following format:

Using the CLEAN function, we can remove the unwanted characters as shown below:

In all the scenario above, the function removed non-printable characters. It also removed the non-printable characters CHAR(15) and CHAR(12).

If we wish to apply this formula quickly on a large amount of data, we can follow the steps below:

1. Assuming the data is given in columns A1 to A1000, then right-click the column header “B” and select “Insert” to create a new “B” column.
2. Now go to B1. Using SHIFT key, select B1 to B1000. In the example, hold “Shift” and click cell “B1000” to select cells “B1” through “B1000.”
3. Now, type “=CLEAN(A1)” (excluding the quotes) and then press “Ctrl-Enter” to apply the CLEAN function to the entire selection and clean every data point on our list.

The function will work even if non-printable characters are present in between the data. Suppose, we are given the following data: ‘Monthlyreport’

In such scenario, we can use the formula CLEAN(Monthlyreport). Using the CLEAN function, we can remove any non-printable data present anywhere between the characters. We get the result shown below:

Example 2

Let’s assume we wish to remove line breaks from cells, we can use TRIM with CLEAN function as shown below:

In the above example, the data include non-printable characters, as well as line breaks. To clean and trim data, we used formula =TRIM(CLEAN(text)) as shown below:

The CLEAN function lacks the ability to remove all non-printing characters, particularly a non-breaking space, which can appear in Excel as CHAR(160). In such scenario, adding the SUBSTITUTE function to the formula, we can remove specific characters as shown below:

Here, we used TRIM, CLEAN and SUBSTITUTE together.

Few things to remember about the CLEAN function

1. The CLEAN function will give the value with all non-printable characters removed.
2. The function was introduced in Excel 2000 and is available in all versions after that.
3. Remember, in the Unicode character set, there are additional non-printing characters. By itself, the CLEAN function does not remove these additional non-printing characters.

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

• Excel Functions for Finance