CLEAN in Google Sheets

What Is CLEAN Function in Google Sheets?

The CLEAN function in Google Sheets removes all non-printable characters from the text data. These characters are sometimes invisible and can appear in the sheets when you copy data from websites or other documents. The CLEAN function is often used alongside other functions to clean up any data copied from different sources. It removes unwanted formatting that could cause issues in processing the data.

In the example below, non-printable characters are line breaks, carriage returns, and other special characters with ASCII characters that can’t be displayed in a visible form.

As seen above, CLEAN in Google Sheets returns the text with the non-printable ASCII characters removed.

The CLEAN function in Google Sheets removes non-printable characters from a string. It ensures that the data does not contain non-printable characters.
Thus, using the CLEAN function in Google Sheets ensures that it enhances the data’s quality and reliability.
Its syntax is as follows: =CLEAN(text). Here, you can place the text directly or use cell references.
Cleaning the data using CLEAN helps in accurate data analysis. Non-printable characters can interfere with functions, formulas, and sorting.
CLEAN works harmoniously with other Google Sheets functions. For comprehensive text processing, combine CLEAN with functions like TRIM to remove excess spaces.

Syntax

The CLEAN function uses a simple syntax. It is as shown below.

=CLEAN(text)

text – The text in the sheet which has non-printable characters to be removed.

How To Use CLEAN Function in Google Sheets?

Using CLEAN in Google Sheets, you can remove non-printable ASCII characters and clean up copied or imported data. You can also remove unwanted formatting. There are two ways to apply the CLEAN function.

Applying CLEAN in Google Sheets Manually

Step 1: Identify Your Data

Enter the text in Google Sheets. Now we must check if the text may contain non-printable characters. These characters are often invisible but can cause unexpected behavior in your data.

Here, we have some text. We used CHAR(10) to print the text from a new line as shown below.

Step 2: Apply the CLEAN Function

Now, let us choose where we want the cleaned text to appear. Here, we choose B3. Type =CLEAN(A1). Press Enter.

Step 3: As A1 contained non-printable characters, they have been removed from the output cell.

Using the Google Menu bar

First, go to the cell where you wish to enter the CLEAN formula.
In the Google menu bar, click on “Insert” ➝ “Function” ➝ “Text” ➝ “CLEAN.”
Enter the required argument. Close the bracket and press the “Enter” key.

Examples

The CLEAN function has several real-world applications that include not just basic text cleaning. Presently, our environment is hugely data-driven and we get information into Google Sheets from numerous sources, thereby giving rise to formatting challenges.

Therefore, it’s important to understand how to apply CLEAN in Google Sheets to streamline data processing workflows. Let us look at a few examples to understand the function’s versatility for cleaning tasks.

Example #1

In this example, we have imported several reviews posted by customers into Column A. Many of these entries contain non-printable characters. These may cause issues with your other third-party tools. Let us look at the function of CLEAN.

Step 1: Here, we must clean the comments to ensure they are free from non-printable characters.

Look at the data.

Step 2: Use CLEAN on the data: In cell B7, enter the formula =CLEAN(A7).

Thus, you can observe the change and how we can use the CLEAN function in Google Sheets to clean all comments.

Example #2 – Combining CLEAN with TRIM Function

The TRIM and CLEAN Google Sheet function is used to remove extra spaces and non-printable characters from text. The syntax of trim is =TRIM(text) while the syntax of CLEAN is =CLEAN(Text). Here, non-printable characters are represented by ASCII numeric codes 0 to 31.

In this example, we use the TRIM and CLEAN functions together to remove extra spaces and non-printable characters from text.

Step 1: Look at the following data in cells A1 and A2. Here, you observe there might be hidden new line characters as there is space below them.

Step 2: Now, use CLEAN in Google Sheets for your data. In cell B1, enter the formula =CLEAN(A1). Press Enter.

Step 3: Now, let us use TRIM and see the difference. Use the following function in B2.

=TRIM(CLEAN(A2)). Press Enter.

Here, you can observe how the spaces have also been trimmed when we use the TRIM function in the second formula. Else, only non-printable characters had been removed as observed in the first case, where the new line character had been removed.

Example #3 – Combining CLEAN with ARRAYFORMULA Function

CLEAN in Google Sheets removes all non-printable characters from text whereas the ARRAYFORMULA function helps apply a function to an entire range at once, rather than individually. Let us look at an example of how you can combine CLEAN with ARRAYFORMULA.

We have a list of stringsfromA1:A5 and we must clean all the text in this range by removing non-printable characters.

Step 1: Now, to clean up this data, apply the following formula in cell B1.

Here,

ARRAYFORMULA: applies the formula to the entire range A1:A5.
CLEAN: It removes any non-printable characters from the text in each cell of the specified range.

You can observe how the formula returns the cleaned version of the text strings, without any non-printable characters (like the tab break CHAR(9) and line break CHAR(10)).

It is very useful in scenarios where you import data from sources such as CSV files where characters could cause formatting issues and helps you clean them up.

Important Things to Note

We use CLEAN in Google Sheets often in conjunction with the TRIM function to remove non-printable characters as well as any extra spaces at the beginning or end of the text to prevent alteration to the data.
CLEAN in Google Sheets removes characters with ASCII values between 0 and 31 but not standard special characters like accented letters, currency symbols, etc.
Remember to use data validation techniques after the clean-up of the data to ensure that any future data that may come up adheres to the rules set up during your clean-up.
It is important to conduct reviews on your data from time to time to ensure that there are no non-printable characters if you are continually importing or copying data from external sources.
Advanced users can use Google Apps Script to automate the cleaning process across multiple sheets or documents.

Frequently Asked Questions (FAQs)

What are the functions with which CLEAN in Google Sheets can be combined?

CLEAN works well when combined with other Google Sheets functions. For text received from third parties, you can combine CLEAN with TRIM in Google Sheets to remove excess spaces before or after the text. The syntax is as follows: =TRIM(CLEAN(A1))

If you need plenty of clean up options for advanced data cleaning requirements, you can use a combination of functions.

=TRIM(CLEAN(SUBSTITUTE(A2, “-“, ” “)))

Here, we clean the text, replace hyphens with spaces, remove any extra space using TRIM, and completely clean up the text.
We also use CLEAN in Google Sheets with the ARRAYFORMULA function to apply it to an entire range of cells at once, rather than individually.

What are the limitations of using the CLEAN function in Google Sheets?

The CLEAN function has some limitations which are as follows:

1. CLEAN can only remove non-printable characters which have ASCII codes 0-31. You cannot remove all possible unwanted characters, such as invisible Unicode characters.
2. To apply the function to remove a wider range of characters, combine CLEAN with other functions such as SUBSTITUTE.

Why is the CLEAN function used?

The CLEAN function targets ASCII characters numbered 0 through 31 in the ASCII character set thus removing non-printable characters from any text. These include line breaks and tabs that can disrupt calculations.
2. CLEAN works diligently on imported data from various sources like CSV files, database exports, etc. This cleaning process ensures consistent formatting across your entire dataset.
3. CLEAN does not wipe away visible text, spaces, or punctuation marks, thereby maintaining the integrity of your data.
4. CLEAN makes text manipulation more reliable by allowing further processing. It removes invisible characters that might interfere with string comparisons or concatenation operations.

Download Template

This article must help understand CLEAN in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

CLEAN In Google Sheets Download

CLEAN in Google Sheets