TRIM and CLEAN in Excel - Step-by-Step Guide

Introduction

Sometimes data copied from other sources has extra spaces or hidden characters that make it hard to work with. The TRIM and CLEAN functions help fix this by removing unwanted spaces and non-printable characters from your text.

When you copy text from websites or PDFs and see extra spaces before, after, or between words

When imported data has strange symbols or invisible characters causing errors

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

When formulas return unexpected results due to hidden spaces or characters

When preparing data for mail merges or reports to ensure neat formatting

Steps

Step 1: Click

- a blank cell where you want the cleaned text

The cell is selected and ready for formula input

Step 2: Type

- the formula bar

The formula appears in the selected cell

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

Step 3: Press

- Enter key

The cell shows the text from A1 without extra spaces

Step 4: Click

- another blank cell

The cell is selected for the next formula

Step 5: Type

- the formula bar

The formula appears in the selected cell

💡 Use =CLEAN(A1) to remove non-printable characters from the text in cell A1

Step 6: Press

- Enter key

The cell shows the text from A1 without hidden characters

Step 7: Combine

- in one formula bar

The cell shows text cleaned of both extra spaces and hidden characters

💡 Use =TRIM(CLEAN(A1)) to do both cleaning steps at once

Before vs After

Before

Cell A1 contains ' Hello World ' with extra spaces and some hidden characters

After

Using =TRIM(CLEAN(A1)) in another cell 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-printable characters from text

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

Common Mistakes

Using TRIM without CLEAN on text with hidden characters

TRIM only removes spaces, so hidden characters remain and can cause errors

Use CLEAN first to remove hidden characters, then TRIM to fix spaces, or combine as =TRIM(CLEAN(A1))

Applying TRIM or CLEAN directly on numbers

These functions work on text, so numbers may not change or cause errors

Convert numbers to text first if needed, or only apply to text cells

Summary

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

CLEAN removes hidden, non-printable characters that can cause problems

Use =TRIM(CLEAN(cell)) to clean text fully before using it in formulas or reports