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("DataAnalysis")
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.