Excel TRIM Function

In Excel, the text may contain unwanted leading or trailing spaces especially if the text is obtained from outer sources like web pages.

Whether the text is based on external sources or written within Excel, you may remove the spaces not only on the left and right of the text but also within the words by using the TRIM function. For example,

After using TRIM()

Not only spaces from left and right but more than one space within the words the in string will also be removed by the TRIM function.

In this tutorial, I will show you examples of trim for removing spaces from both sides, within the text of the text string along with only removing spaces from the left of the text.

The Syntax of TRIM function

The general syntax of the TRIM function is:

TRIM(text)

You may refer to the cell or use text as constant literal.

The example of removing spaces by TRIM

In this example, the text is used in the TRIM function that contains leading and trailing spaces (spaces towards the left and right of the string). The formula for removing spaces:

The result of the TRIM function:

You can see the difference in how the TRIM function deleted the spaces from the left and right of the given text.

Referring Cell text in the TRIM function example

The following example shows using the cell reference in the TRIM formula. In the A3 cell, I entered the following text:

The spaces are given on the right and left sides and also between the words in the text. See how the TRIM function removed these spaces:

You can see, after using the TRIM function, the spaces from left and right sides are removed and extra spaces are removed from the words.

An example of removing leading spaces

In certain situations, you may only require removing the leading spaces (the spaces towards left side). Excel has no direct function like LTRIM or RTRIM to remove spaces from left or right only, respectively.

However, you may use different formula combinations to achieve that. In the following example, the TRIM function is used with LEFT, FIND, and REPLACE functions to remove leading spaces.

The formula:

You can see, as I entered the following text in the A3 cell:

It removed only spaces from the left side. The resultant string is:

Trimming the number columns

The columns that contain numbers may also contain unwanted space. You may use the TRIM function to remove spaces for the numeric column just like the text. However, be careful, as simply using TRIM makes numbers as text.

You may notice the numbers aligned left after using TRIM whereas the default behavior is aligning numbers right.

See an example below:

You can see numbers are displayed left aligned while the SUM of B2 to B6 is 0.

The issue can be sorted by using the VALUE function in conjunction with the TRIM function as shown below:

You can see that the A column contains raw numbers (without any formula with spaces). The B column displayed the number after using TRIM function only. The sum is displayed as 0.

The C column displays numbers by using this formula:

It goes from C2 to C6 and we got the sum that is correct along with alignment.

An example of TRIM with non-ASCII space

As mentioned earlier, the TRIM function is designed to remove spaces for ASCII character (32). If you try removing spaces with non-ASCII spaces then it will not. This generally happens due to non-breaking space i.e. CHAR(160) that results if you copy text from the webpages.

To demonstrate that, I have copied a text line from a web page that contained leading, trailing, and extra spaces within words. The web page spaces are given by “ ”. See the text and output as I applied TRIM formula for trimming that text:

You can see that even after using the TRIM function, no spaces are removed. This is how the web page text is created and copied from the browser:

How to sort this issue for removing spaces?

You may use the SUBSTITUTE function that will first replace the non-breaking space character CHAR(160) with an ASCII space character and then TRIM function will remove those spaces. See the formula and output with comparison:

The SUBSTITUTE/TRIM formula:

The result:

You can see the spaces are removed from the same web page text.