Excel TRIM Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 90 of 105 · Full Excel course

The Excel TRIM function removes all leading and trailing spaces from a text string and reduces any run of spaces between words to a single space. It returns the cleaned text. TRIM is the first function to apply to data typed by hand or imported from other systems, because stray spaces are the most common reason that lookups, filters and duplicate checks fail.

Table of Contents

Syntax

=TRIM(text)

Arguments

Argument	Required / Optional	Meaning
text	Required	The text string, or a reference to a cell, from which you want spaces removed.

How TRIM works

TRIM removes the ordinary space character, code 32, from the start and end of the text and collapses internal groups of spaces to one. It does not touch tabs, line breaks or the non-breaking space (code 160) that web pages and HTML emails use, so text copied from a browser often still looks padded after TRIM. Numbers passed to TRIM are returned as text: =TRIM(" 42 ") gives the text “42”, which needs VALUE before it can be summed.

Step-by-step example: fix a lookup that returns #N/A

A product list in column A was pasted from an email. =VLOOKUP("Laptop",A2:B50,2,FALSE) returns #N/A although “Laptop” is visibly present.
In C2 enter =LEN(A2) and in D2 enter =LEN(TRIM(A2)). C2 shows 8 and D2 shows 6, revealing two hidden spaces.
In E2 enter =TRIM(A2) and fill down.
Copy column E, paste as values over column A and delete the helper columns. The VLOOKUP now works.

Practical use cases

1. Standard clean-up of imported text

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

SUBSTITUTE converts non-breaking spaces, CLEAN removes control characters and TRIM tidies the spaces.

2. Trim inside a lookup without a helper column

=XLOOKUP(TRIM(E2),TRIM(A2:A500),B2:B500)

Both the lookup value and the lookup range are trimmed on the fly. In Excel 2019 use INDEX and MATCH with Ctrl+Shift+Enter.

3. Count words accurately

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

4. Detect cells that need trimming

=LEN(A2)<>LEN(TRIM(A2))

Returns TRUE where TRIM would change the text; use it in a filter or conditional formatting rule.

5. Convert trimmed numbers back to values

=VALUE(TRIM(A2))

Common mistakes and tips

Spaces remain after TRIM: they are non-breaking spaces (CHAR(160)). Replace them with SUBSTITUTE first.
Numbers turn into text: TRIM always returns text. Wrap in VALUE or add 0.
Source cell unchanged: TRIM returns a new value; paste as values to replace the original data.
Line breaks are kept: TRIM does not remove CHAR(10). Use CLEAN or SUBSTITUTE.
Bulk alternative: Power Query’s Transform > Format > Trim applies the same rule on import and removes the need for helper columns.

TRIM compared with CLEAN and Find and Replace

TRIM handles spaces, CLEAN handles non-printing control characters and SUBSTITUTE handles specific characters such as the non-breaking space. Most real data problems need two or all three together, which is why TRIM(CLEAN(SUBSTITUTE(...))) is a standard pattern. Find and Replace (Ctrl+H) can remove double spaces but cannot reliably remove only the leading and trailing ones, and it changes the data permanently. TRIM is a live formula that recalculates whenever the source changes, which makes it the safer choice in templates. Note also that Excel’s TRIM differs from the TRIM in most programming languages: it collapses internal spaces as well as removing the outer ones. If internal double spaces must be preserved, use SUBSTITUTE patterns or Power Query instead.

Related functions

CLEAN: removes non-printing characters that TRIM leaves behind.
SUBSTITUTE: replaces non-breaking spaces before trimming.
LEN: compares lengths to detect extra spaces.
VALUE: converts trimmed numeric text back to a number.
See all lessons in the Excel Formulas course.

Frequently asked questions

Why does TRIM not remove all the spaces in my cell?

The remaining characters are probably non-breaking spaces (CHAR(160)) from web or HTML sources. Use =TRIM(SUBSTITUTE(A2,CHAR(160),” “)) to remove them.