Excel TRIM Function Examples

These Excel TRIM function examples show how you can remove space characters from text, to clean up your data. There is a video, written steps, and a sample file for free download.

Video: TRIM Function

The Excel TRIM function removes all the spaces in a text string, except for single spaces between words.

In this video you'll see how to use the TRIM function to:

• remove spaces from the start and end of a text string
• remove all except single spaces between words
• you'll see that TRIM does NOT remove some special space characters that have been copied from websites.

There are written instructions below the video.

Uses for TRIM

The TRIM function can help with the cleanup of text that you've downloaded from a website, or imported from another application. The TRIM function:

• Removes spaces from start and end of text string
• Removes all except single spaces between words
• Does NOT remove some special space characters copied from websites

TRIM Syntax

The TRIM function has the following syntax:

• TRIM(text)
  • text is a cell reference, or text string, from which you want spaces removed.

TRIM Traps

The TRIM function only removes standard space characters from the text.

If you copy text from a website, it might contain special non-breaking space characters, and the TRIM function will not remove those.

Example 1: Remove Start/End Spaces

The TRIM function will remove all the space characters at the start and end of a text string.

For example, in cell C5 in the screenshot below, there are 2 extra spaces at the start of the text, and 2 exgtra spaces at the end of the text. In cell E5, the LEN function calculates how many characters are in cell C5, and the result is 17 characters.

The TRIM function in cell C7 removes those 4 spaces: =TRIM(C5)

In cell E7, the LEN function calculates how many characters are in cell C7, and the result is 13 characters -- the extra spaces at the start and end were removed.

.

Example 2: Remove Extra Spaces

You can also use the TRIM function to remove extra space characters between words in a text string.

In the screenshot below, there are 3 extra spaces between the words in cell C5. In cell E5, the LEN function calculates how many characters are in cell C5, and the result is 20 characters.

The TRIM function in cell C7 removes those extra spaces, as well as the 2 spaces at the start and 2 spaces at the end of the text string.

=TRIM(C5)

In cell E7, the LEN function calculates how many characters are in cell C7, and the result is 13 characters -- the extra spaces between the words, and the spaces at the start and end were removed.

Example 3: Non-Breaking Spaces

It's important to know that the TRIM function does NOT remove some space characters, such as a non-breaking space copied from a website. The video below shows how to remove those characters.

For example, in this screenshot, cell C5 contains one non-breaking space. In cell E5, the LEN function calculates how many characters are in cell C5, and the result is 14 characters.

That non-breaking space is NOT trimmed by the TRIM function: =TRIM(C5)

In cell E7, the LEN function calculates how many characters are in cell C7, and the result is 14 characters -- the non-breaking spaces was not removed.

Remove Non-Breaking Spaces

Although the TRIM function won't remove them, you can manually delete the non-breaking space character, or use the SUBSTITUTE function.

In this video you'll see how to use SUBSTITUTE to change a region name in a report title, remove non-printing characters, and substitute a space character

Get the Sample Files

To see the formulas used in today's examples, you can get the TRIM function sample workbook.

The file is zipped, and is in xlsx file format, and the file does not contain any macros.

More Functions Tutorials

TEXT Function

SUBSTITUTE function

Text Function Videos