Cellwise Try it free

Text function

Excel TRIM function

TRIM removes spaces from the start and end of text and shrinks any run of spaces between words down to one. It is the first thing to try when a lookup fails on data pasted from another system.

=TRIM(text)

Arguments

text
The text or cell to clean up.

TRIM examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1Imported nameCodePrice
2 Olivia Brown K-220 18
3Ethan ClarkK-31042
4 Zoe MartinK-41527
  1. =TRIM(A2)

    Result: Olivia Brown

    Removes the leading, trailing and double spaces: "Olivia Brown".

  2. =LEN(A2)-LEN(TRIM(A2))

    Result: 5

    Shows how many extra spaces were hiding in the cell: 5.

  3. =TRIM(B2)=B2

    Result: FALSE

    FALSE: the original code has a hidden trailing space, so it differs from its trimmed version.

  4. =TRIM(B2)="K-220"

    Result: TRUE

    Trim first and the match works: TRUE.

How to use TRIM

  1. Insert a helper column next to the messy data.
  2. Type =TRIM( and select the first cell.
  3. Close the bracket and fill down.
  4. Copy the column and Paste Values over the original.

Common TRIM mistakes

Non-breaking spaces from web pages

TRIM only removes normal spaces (code 32). Replace character 160 first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Expecting single spaces between words to go

TRIM keeps one space between words. To remove all spaces use SUBSTITUTE(A2," ","").

Line breaks remain

TRIM does not remove line breaks. Use CLEAN as well: =TRIM(CLEAN(A2)).

TRIM FAQ

Why is TRIM not removing spaces in Excel?

The spaces are probably non-breaking spaces (CHAR(160)) from a web page. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

How do I remove trailing spaces in Excel?

Use =TRIM(A2) in a helper column, then paste the results as values over the original.

Why does my VLOOKUP fail when the values look the same?

Often one side has a hidden trailing space. Wrap the lookup value in TRIM, or clean the table column with TRIM first.

Practice TRIM in these lessons

Guides that use TRIM

Related functions

Updated