TRIM and CLEAN in Google Sheets - Step-by-Step Guide

Introduction

Sometimes data copied from other sources has extra spaces or hidden characters that make it look messy. The TRIM and CLEAN functions help fix this by removing unwanted spaces and invisible characters, making your data neat and easier to use.

When you copy text from a website and it has extra spaces before or after words.

When imported data has strange invisible characters causing errors in formulas.

When you want to clean up names or addresses before sorting or filtering.

When you receive data from others and it looks messy with extra spaces or line breaks.

When you want to prepare text data for better display or printing.

Steps

Step 1: Click

- a blank cell where you want the cleaned text to appear

The cell is selected and ready for formula input

Step 2: Type

- the formula bar

Formula is entered for cleaning text

💡 Start with =TRIM(A1) to remove extra spaces from cell A1

Step 3: Press

- Enter key

The cell shows the text from A1 without extra spaces

Step 4: Click

- another blank cell

Cell is ready for formula input

Step 5: Type

- the formula bar

Formula is entered to remove invisible characters

💡 Use =CLEAN(A1) to remove non-printing characters from cell A1

Step 6: Press

- Enter key

The cell shows the text from A1 without invisible characters

Step 7: Type

- formula bar

Both extra spaces and invisible characters are removed

💡 Use =TRIM(CLEAN(A1)) to clean and trim text in one step

Before vs After

Before

Cell A1 contains ' Hello World ' with extra spaces and line breaks

After

Using =TRIM(CLEAN(A1)) shows 'Hello World' with no extra spaces or hidden characters

Settings Reference

TRIM function

📍 Formula bar

Removes extra spaces except single spaces between words

Default: No default, you must specify the text or cell

CLEAN function

📍 Formula bar

Removes non-printing characters like line breaks and tabs

Default: No default, you must specify the text or cell

Common Mistakes

Using TRIM alone when data has invisible characters

TRIM removes spaces but not hidden characters like line breaks

Use CLEAN first to remove hidden characters, then TRIM to fix spaces

Applying CLEAN without TRIM on text with extra spaces

CLEAN removes invisible characters but leaves extra spaces intact

Combine CLEAN and TRIM to fully clean text

Summary

TRIM removes extra spaces from text, keeping only single spaces between words.

CLEAN removes invisible, non-printing characters that can cause problems.

Use =TRIM(CLEAN(cell)) to clean and tidy text data in one step.