TRIM function – Explained with Examples

Excel’s TRIM function removes unwanted spaces around text, except for single spaces between text.

This will include removing all leading or trailing spaces in input text (before or after any word or series of words), or by removing multiple spaces between words, leaving only a single space.

Function Syntax

=TRIM(text)
  • The text argument is required.

Usage

What TRIM does

  1. TRIM will remove leading or trailing space characters in text, that is spaces at the beginning or end of text.
  2. TRIM will remove multiple space characters between words in text, but will leave a single space.

What TRIM does not do

  1. TRIM will not remove non-printing characters from strings
  2. TRIM will not remove tab characters from strings
  3. TRIM will not remove non-break space characters from strings

Examples

We will use Notepad++, a useful and free plain text editor for visualising text based files, to visualise examples before applying the TRIM function in Excel.

  1. Text with a single space between.
  2. Text with Leading spaces
  3. Text with Trailing spaces
  4. Text with Leading and trailing spaces
  5. Text with Multiple spaces between words
  6. Text with a Tab character and with multiple spaces in text

Using Notepad++‘s Show All Characters option we can set up our example with various distributions of spaces in text. We’ll also include a Tab in one example to see what happens.

TIP: Notepad++ is a very useful tool for inspecting data you have especially to check for the presence of errant characters.

Example data containing leading, trailing and duplicate spaces, and a Tab character

Spaces are indicated by orange dots and the tab symbol by an orange arrow, we will copy this data and paste it to Excel.

Note: CR and LF are ‘Carriage Return’ and ‘Line Feed’ visualisations in Notepad++, these are removed as the rows are converted to Cell rows in Excel.

We’ll use the LEN function to count all characters in the text including the spaces and Tab character.

=LEN(text)

In Excel, we add the following

  1. Column A: We paste our test data from Notepad++ (above),
  2. Column B: We use the LEN function to count both printing and non printing characters in the Column A input data,
  3. Column C: We apply TRIM to our input data (Column A)
  4. Column D: We count the characters remaining after using the TRIM function on our input data, from Column C, with a second LEN function.

NOTE: The Tab character is a not visible in the cell, but its presence can be seen in the formula bar as indicated by the red arrow and yellow highlighted area, and the character count.

Using the TRIM function on our test data

  1. Row 2: TRIM has no effect on space between with only a single space between words
  2. Row 3: TRIM removes the leading space, which results in one fewer characters in the output
  3. Row 4: TRIM removes the trailing space, which results in one fewer characters in the output
  4. Row 5: TRIM removes the duplicate multiple spaces between words, which results in one fewer characters in the output
  5. Row 6: TRIM has no effect on tab between and the Tab character remains between the words, the character count remains the same.

Result

Therefore, we have shown how the TRIM function:

  • removes all excess spaces around text, except for single spaces between words.
  • The TRIM function does not remove other ‘spacer type’ characters, such as the tab character.
Column C output after TRIM function applied – views in Notepad++

Related Topics

F006