CLEAN Function

What is the CLEAN Function?

The CLEAN Function[1] is categorized under Excel Text functions. The function removes non-printable characters from the given text. As financial analysts, we often import data from various sources and the CLEAN function can help remove nonprintable characters from a 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 to 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 the 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 a 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 the 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 a scenario, adding the SUBSTITUTE function to the formula, we can remove specific characters as shown below:

Here, we used TRIM, CLEAN, and SUBSTITUTE together.

A 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.

Click here to download the sample Excel file

Additional Resources

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 CFI resources:

• Excel Functions for Finance
• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must Know
• Excel Shortcuts for PC and Mac
• See all Excel resources