How to Remove Non-Printable Characters and Extra Spaces in Google Sheets Using CLEAN and TRIM

Importing data into Google Sheets from external databases, web scrapes, enterprise resource planning (ERP) platforms, or plain text CSV files frequently introduces hidden formatting defects. Among the most frustrating of these issues are rogue non-printable characters and irregular whitespace padding. Non-printable characters—such as ASCII control characters, page breaks, and line feeds—can disrupt formula lookups like VLOOKUP or XLOOKUP, while trailing or multiple consecutive spaces distort cell presentation and cause exact string matching comparisons to fail silently.

Google Sheets provides two essential text sanitisation functions specifically designed to tackle these data cleansing challenges: CLEAN and TRIM. Used independently or nested together, these functions automate the extraction of formatting artifacts, restoring raw text datasets into clean, reliable inputs ready for financial analysis, pivot tables, and automated reporting.

Understanding the Difference Between CLEAN and TRIM

Although both functions process and sanitise raw string data, they target fundamentally different categories of text contamination:

• CLEAN: Removes non-printable characters with ASCII values 0 through 31, as well as ASCII 127. These control characters (including null bytes, bell alerts, carriage returns, and form feeds) are invisible on screen but occupy byte space within strings, causing formulas to evaluate otherwise identical text as distinct values.
• TRIM: Strips all leading and trailing whitespace from a cell and condenses any internal clusters of multiple consecutive space characters down to a single standard space. It does not alter regular letters, numbers, or internal punctuation.

Because datasets exported from legacy servers frequently suffer from both non-printable control bytes and uneven spacing, nesting both formulas together creates an all-in-one sanitisation pipeline.

How to Use the CLEAN Function in Google Sheets

The syntax for the CLEAN function is straightforward:

=CLEAN(text)

Here, text can refer to a direct string enclosed in quotation marks or, more commonly, a reference to a cell containing uncleaned data (for example, A2).

1. Open your spreadsheet containing the raw imported text.
2. Select an empty adjacent column to host your cleaned results (for example, cell B2).
3. Enter the formula:

   =CLEAN(A2)

4. Press Enter. Google Sheets evaluates the reference and displays the string stripped of all low-order ASCII non-printable control characters.
5. Double-click the small blue fill handle in the bottom-right corner of cell B2 to copy the formula down the entire dataset column.

How to Use the TRIM Function in Google Sheets

The TRIM function handles irregular whitespace padding without affecting standard spacing between words:

=TRIM(text)

To sanitise padded strings:

1. Click into cell B2 next to your target data.
2. Type the formula:

   =TRIM(A2)

3. Press Enter. Any unwanted leading spaces before the first character, trailing spaces after the final character, or triple spaces between words will immediately be compressed and cleaned.
4. Fill the formula downward across your remaining rows.

Combining CLEAN and TRIM for Comprehensive Sanitisation

In real-world data engineering and spreadsheet management, applying both functions simultaneously ensures that neither hidden control codes nor irregular whitespace survive into your production models. Nesting the functions operates seamlessly:

=TRIM(CLEAN(A2))

In this nested construction, Google Sheets first strips all non-printable ASCII codes via CLEAN. The resulting string is immediately passed into TRIM, which removes any leftover leading, trailing, or double spaces. The final output is a completely normalised text string.

Automating Entire Columns Using ARRAYFORMULA

Rather than manually dragging formula fill handles down hundreds or thousands of rows, you can wrap your sanitisation formula inside ARRAYFORMULA. This dynamically calculates every row in the column and automatically processes newly added rows:

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", TRIM(CLEAN(A2:A))))

To implement this automation:

1. Select cell B2 at the top of your helper column and clear any existing formulas below it.
2. Paste the formula above into the formula bar.
3. Press Enter. The formula evaluates the entire range from row 2 downward. If cell A is blank, it outputs an empty string; if cell A contains text, it cleans and trims the content instantaneously without requiring individual formulas in every row.

Handling Non-Breaking Spaces (CHAR 160)

A common pitfall encountered when sanitising text copied from web pages or HTML emails is the non-breaking space ( ), represented by ASCII/Unicode character code 160. Because the standard TRIM function strictly targets standard spaces (ASCII 32), character 160 will remain in the cell, continuing to break lookups.

To eliminate non-breaking spaces alongside standard control codes, combine SUBSTITUTE with CHAR(160) inside your cleaning formula:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

This formula replaces every non-breaking space with a regular space first, allowing TRIM and CLEAN to finish the normalisation process cleanly.

Replacing Formulas with Static Values

Once your text dataset has been sanitised, keeping dynamic formulas in helper columns can add unnecessary recalculation overhead to large workbooks. Converting your results to permanent values ensures stability:

1. Highlight the column of sanitised cells.
2. Press Ctrl + C (or Cmd + C on Mac) to copy the data.
3. Right-click the destination column where you want the final text to live.
4. Select Paste special > Values only (or press Ctrl + Shift + V / Cmd + Shift + V).
5. You can now safely delete the original corrupted data column and helper columns without impacting your workbook.