The CLEAN function is one of the Text functions in Google Sheets. What is the use of it? To understand that you should know about the non-printable characters which you may come across in Sheets.

The Clean function is useful for removing non-printable characters in a cell or in an array. It’s not an array function though.

So obviously, when the non-printable characters are present in an array, then using the ArrayFormula function is unavoidable. I have included this Array and Clean combination in the below example.

Non-printable Characters in Google Sheets

Non-printable characters are the non-printable ASC-II characters such as Backspace, Line Feed, Carriage Return, Horizontal Tab, Vertical Tab, etc.

Refer this Wiki to see the table containing the current Unicode characters. In the provided table in that page, the decimal numbers 0 to 31 represents the non-printable characters in Google Sheets. You can test that in Google Sheets as below.

=ArrayFormula(char(row(A1:A50)))

Apply this formula in cell A1. Please note that column A must be blank at least up to row#50.

Scroll down this column to see the visible characters starting from row # 33.

What does the above formula do?

The Row function returns the numbers 1 to 50 in the range A1:A50. That numbers are within the Char function to return the associated characters.

That means the formula returns the non-printable characters in the range A1:A31. The balance rows A32:A50 contains the printable characters.

As a side note, the character in row # 32 (A32) is also not visible but that is a printable character called Whitespace.

Example to Non-Printable Characters in a Text in Sheets

Your Sheet may contain non-printable characters if you import data from external sources. Also, you can manually place such characters using the Char function in Sheets.

For example, I have the string “Info Inspired” in cell A1. I can move the string ‘Inspired’ to a new line within cell A1 as below.

="INFO"&CHAR(10)&"INSPIRED"

The Char function converts the number 10 to the character ‘Line Feed’ as per the Unicode table.

You May Like: How to Move New Lines in a Cell to Columns in Google Sheets.

If such characters present in a cell, it won’t be ‘visible’ similar to the Whitespace character. But the Whitespace is not a non-printable character.

Syntax and Examples to the Clean Function in Google Sheets

Syntax:

CLEAN(text)

Argument:

text – The text string in a cell or array whose non-printable characters are to be removed.

Formula Example to the Clean Function in Sheets

=clean(A1)

In an array use the function as per the formula example below.

=ArrayFormula(clean(A1:A10))

Note:

Just like the To_Text function, you can convert a number to text using the Clean function. Want to see one example? Here you go!

Assume cell A1:A10 contains the numbers 1 to 10. You can use the following Clean formula to convert these numbers to text.

=ArrayFormula(clean(A1:A10)) 

Using a Clean formula, you can’t Clean the Whitespaces in a cell. For that, you can either use the Trim function or the menu Data > Trim Whitespace command.

=TRIM(A1)

Find more details here – How to Remove Additional, Double or Extra Spaces in Google Sheets.

That’s all about the use of Clean function in Google Sheets. Enjoy!