The Google Sheets function CLEAN is a valuable tool for data preparation. It efficiently removes non-printable ASCII characters from text, ensuring that your data is clean and suitable for further analysis or presentation. This can be particularly helpful when importing data from external sources where formatting issues might arise.

Syntax

CLEAN(text)

• text: The cell reference or string from which non-printable characters will be removed.

Example #1

=CLEAN(A1)

This function removes non-printable characters from the text in cell A1. For example, if A1 contains “HelloWorld”, the result would be “HelloWorld”.

Example #2

=CLEAN("DataAnalysis")

Here, the CLEAN function eliminates the non-printable character “”. The output would be “DataAnalysis”, providing you with a clean string for analysis.

Example #3

=CLEAN("Welcome
to Google Sheets")

In this case, the function cleans the input by removing the newline character ” “, resulting in the string “Welcometo Google Sheets” for easier readability.

Error handling

• VALUE!: This error occurs when the input is not a valid text type, such as a date or number.
• NAME?: This error indicates that Google Sheets does not recognize the function name, possibly due to a typo.
• REF!: This error happens when a cell reference is not valid, such as when the referenced cell is deleted.

Conclusion

The CLEAN function in Google Sheets is an essential tool for maintaining clean datasets by removing non-printable characters. By utilizing this function, users can enhance the quality of their text data, paving the way for accurate analysis and presentation. Whether you’re working with imported data or cleaning up internal spreadsheets, CLEAN helps you achieve a polished final output.