Exceljet

Quick, clean, and to the point

Excel TRIM Function

Excel TRIM function
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)
Arguments 
  • text - The text from which to remove extra space.
Version 
Usage notes 

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.