CLEAN

The CLEAN function in Google Sheets removes non-printable ASCII characters from a text string, which is incredibly useful when working with data imported from external systems, PDFs, or databases. If your text contains strange invisible characters causing formulas to break, CLEAN is often the fix. Dive into our comprehensive guide to master its use.

Parameters

• text: The text or cell reference from which to remove non-printable characters.

Step-by-Step Tutorial

1. Removing non-printable characters from text:

   • Example: If A1 contains "HelloWorld" (with a hidden control character), then =CLEAN(A1) will return "HelloWorld".

2. Cleaning imported data before further processing:

   • Example: =TRIM(CLEAN(A1)) combines CLEAN with TRIM to remove both non-printable characters and extra spaces.

Use Cases and Scenarios

1. Data Import Cleanup: Sanitize text pulled from CSV exports, databases, or web scraping tools.
2. Preprocessing for Formulas: Ensure text comparisons and lookups work correctly by removing hidden characters that can cause silent mismatches.
3. Report Preparation: Clean up copy-pasted content before presenting or sharing data.

Related Functions

• TRIM: Removes leading, trailing, and repeated spaces from text.
• SUBSTITUTE: Replaces existing text with new text in a string.
• REGEXREPLACE: Replaces part of a text string with a different string using regular expressions.

Related Articles

• Working with Text Functions in Google Sheets: A Comprehensive Guide