Excel TRIM Function — Remove Extra Spaces (with Examples)

Contents

What does TRIM do?
Syntax
6 real examples
TRIM in data cleaning workflows
What TRIM cannot do
FAQ

What does TRIM do?

TRIM removes extra spaces from text — spaces at the start, spaces at the end, and multiple consecutive spaces in the middle (colapsing them to a single space). It leaves exactly one space between words and nothing before or after.

This sounds minor but it is one of the most important data cleaning formulas in Excel. Extra spaces are invisible — you cannot see them — but they silently break VLOOKUP matches, COUNTIF counts, and text comparisons. A name with a trailing space looks identical to one without but will never match in a lookup.

Syntax

=TRIM(text)

Argument	Description
text required	The text string or cell reference to remove extra spaces from.

6 real examples

Example 1

Remove leading and trailing spaces

=TRIM(A2) ← " John Smith " → "John Smith"

Example 2

Remove double spaces between words

=TRIM(A2) ← "John Smith" (2 spaces) → "John Smith" (1 space)

TRIM collapses all multiple spaces between words to a single space, not just leading and trailing ones.

Example 3

Fix VLOOKUP that returns #N/A due to spaces

If your lookup is failing for values that look identical, spaces are usually the cause.

=VLOOKUP(TRIM(A2), $C:$D, 2, 0)

TRIM the lookup value before passing it to VLOOKUP. Also TRIM the lookup column itself if spaces are on that side.

Example 4

Combine TRIM with UPPER for standardisation

=TRIM(UPPER(A2))

Remove spaces AND standardise capitalisation in one formula. Essential when merging data from multiple sources where entries were typed inconsistently.

Example 5

TRIM in a COUNTIF to avoid missed counts

=COUNTIF(A2:A100, TRIM(E1))

If E1 has extra spaces, the COUNTIF would miss matches. TRIM ensures the criteria is clean before counting.

Example 6

Check if TRIM is needed

Use LEN to detect hidden spaces — if LEN differs from the trimmed version, spaces are present.

=IF(LEN(A2) <> LEN(TRIM(A2)), "Has spaces", "Clean")

TRIM in data cleaning workflows

When you import data from external systems — CSV files, database exports, web scrapes, copied text — extra spaces almost always come along for the ride. A standard data cleaning routine looks like this:

Step 1: Remove spaces =TRIM(A2) Step 2: Standardise capitalisation =PROPER(TRIM(A2)) Step 3: Remove line breaks (TRIM doesn't catch these) =TRIM(SUBSTITUTE(A2, CHAR(10), " "))

What TRIM cannot do

TRIM only removes the standard space character (ASCII 32). It does not remove:

Non-breaking spaces — common in text copied from websites (ASCII 160). Use =SUBSTITUTE(A2, CHAR(160), "") to remove these.
Line breaks — use =SUBSTITUTE(A2, CHAR(10), " ") to replace line breaks with spaces, then TRIM.
Tabs — use =SUBSTITUTE(A2, CHAR(9), "")

💡 The complete cleaning formula

Combine TRIM with SUBSTITUTE to handle all invisible characters: =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(10)," "))

FAQ

Why does TRIM not fix my VLOOKUP?

You may have non-breaking spaces (CHAR 160) rather than regular spaces. TRIM only removes standard spaces (CHAR 32). Try =SUBSTITUTE(A2,CHAR(160),"") to remove non-breaking spaces, then TRIM.

Does TRIM work on numbers?

Yes, but TRIM converts numbers to text. If A2 contains the number 42 with leading spaces, =TRIM(A2) returns "42" as text, not the number 42. Wrap with VALUE to convert back: =VALUE(TRIM(A2)).

How do I TRIM an entire column permanently?

Add a helper column with =TRIM(A2), copy it, then Paste Special → Values Only over column A. Now the trimmed values replace the originals and you can delete the helper column.

Can TRIM remove all spaces, not just extra ones?

No — TRIM keeps single spaces between words by design. To remove ALL spaces use =SUBSTITUTE(A2," ","") which replaces every space with nothing.

Practise data cleaning formulas

ExcelPro has TRIM, PROPER, SUBSTITUTE and UPPER exercises in every track. Real messy data, instant feedback, free to start.

Try cleaning exercises →

Related formulas

UPPER LOWER PROPER SUBSTITUTE LEN VALUE

The Excel TRIM Functionexplained simply