Remove Extra & Hidden Spaces (TRIM + CLEAN)

Excel Formulas › Text

All versionsTRIM

Data pasted from the web or other systems is full of stray spaces and invisible control characters that break lookups and totals. TRIM collapses extra spaces and CLEAN strips non-printing characters — together they scrub text spotless.

Quick formula: to clean messy text in A2:
=TRIM(CLEAN(A2))
TRIM removes leading, trailing, and doubled spaces (leaving single spaces between words); CLEAN removes non-printing characters like line breaks and tabs.

Functions used (tap for the full reference guide):

TRIMCLEAN

The example

Messy imported names cleaned to a tidy form.

AB
1RawCleaned
2  Ann Lee Ann Lee
3Bo   NgBo Ng

The formula

Strip spaces and hidden characters in one pass:

=TRIM(CLEAN(A2)) // " Ann Lee " → "Ann Lee"

How it works

Each function tackles a different kind of junk:

  1. TRIM removes leading and trailing spaces and reduces any run of spaces between words to a single space.
  2. CLEAN removes non-printing characters (codes 0–31) — line breaks, tabs, and other control characters that sneak in from imports.
  3. Nesting them — TRIM(CLEAN(A2)) — cleans hidden characters first, then collapses the spaces left behind.
  4. Paste the result back as values to replace the messy originals once you’re happy.

TRIM doesn’t catch every space. The non-breaking space (CHAR(160), common in web copy) survives TRIM. Strip it first: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).

Try it: interactive demo

Live demo

Type text with extra spaces; see it cleaned.

Cleaned: “”   ( chars)

Variations

Kill non-breaking spaces too

Handle CHAR(160) from web copy:

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

TRIM only

If there are no control characters:

=TRIM(A2)

Count spaces removed

How much junk there was:

=LEN(A2) - LEN(TRIM(CLEAN(A2)))

Pitfalls & errors

Non-breaking spaces survive TRIM. CHAR(160) looks like a space but isn’t one to TRIM. SUBSTITUTE it to a normal space first.

TRIM keeps single internal spaces. It only collapses extra spaces — “New  York” becomes “New York,” not “NewYork.”

CLEAN misses some modern characters. It removes codes 0–31 but not every invisible Unicode character; for stubborn cases, target the specific CHAR code with SUBSTITUTE.

Practice workbook

📊
Download the free Remove Extra & Hidden Spaces (TRIM + CLEAN) practice workbook
A text-cleaning sheet with TRIM+CLEAN, the non-breaking-space and count-removed variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I remove extra spaces in Excel?
Use =TRIM(A2) to remove leading, trailing, and doubled spaces. Wrap with CLEAN — =TRIM(CLEAN(A2)) — to also strip non-printing characters from imports.
Why does TRIM leave some spaces behind?
Web-copied text often contains non-breaking spaces (CHAR(160)) that TRIM ignores. Replace them first: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
What's the difference between TRIM and CLEAN?
TRIM handles spaces; CLEAN removes non-printing control characters like line breaks and tabs. Use both together for thorough cleaning.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Remove line breaks · Remove specific characters · Proper case

Function references: TRIM · CLEAN