Summary

The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.

Purpose 

Remove extra spaces from text

Return value 

Text with extra spaces removed.

Syntax

=TRIM(text)
  • text - The text from which to remove extra space.

How to use 

The TRIM function strips extra spaces from text, leaving only a single space between words, and removing any leading or trailing space. For example:

=TRIM("   A stitch    in time.   ")  // returns "A stitch in time."

The TRIM function can be used together with the CLEAN function to remove extra space and strip out other non-printing characters:

=TRIM(CLEAN(A1)) // trim and clean

TRIM often appears in other more advanced text formulas. For example, the formula below will count the number of words in cell A1:

LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Because this formula depends on single spaces to get an accurate word count, TRIM is used to normalize space before the count is calculated. Full description here.

Notes

  • TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text.
  • TRIM is useful when cleaning up text that has come from other applications or environments.
  • TRIM only removes the ASCII space character (32) from text.
  • Unicode text often contains a non-breaking space character (160) that appears in web pages as an HTML entity. This will not be removed with TRIM. 
  • The CLEAN function strips the first 32 non-printing characters (ASCII values 0 through 31) from text.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.