How to Use TRIM Function in Excel

When you import text from the internet or another application into your Excel spreadsheet, it often comes with unwanted spaces either before a text, after a text, or in the middle of the text value. Excel gives you a simple, easy-to-use function called TRIM to clean up those extra spaces.

With Excel’s TRIM function, you can easily remove the spaces not only at the beginning and end of the text but also within the text string. In this post, we will show you how to remove the leading and trailing space of the text, and space within the text along with only remove spaces from the left of the text.

Using TRIM function to Remove Extra Spaces in Excel

TRIM is a String/Text function that removes not only the spaces from both sides but more than one space within the words too. This function can only remove the ASCII space character (32) from the text string but not the non-breaking space characters that are usually found in webpages and copied to Excel.

The syntax of TRIM function is:

=TRIM(Cell Value/Text)

You can refer to the cell or use direct text as argument in the function.

The following sample sheet has leading, trailing, double space, space in between, and many extra spaces in the cells. Let’s see how we can use TRIM to remove extra spaces.

Select the cell where you want your trimmed text string and type the formula in the following picture. In our example, we want to trim the text string in cell A1, so we used A1 as the argument of the TRIM function and typed the formula in cell B.

As you can see all leading, trailing, and double spaces are removed in the text string.

You can also enter text string instead of cell reference as the argument in the function. Just make sure to enclose your text string with double quotation marks (“”) as shown below.

Using TRIM function to Remove Extra Spaces in Multiple Cells

You can also use TRIM to removes unwanted spaces in a column of cells. You just have to apply the formula you typed in one to the rest of the column.

You can see a little green square (fill handle) at the lower right corner of the formula cell, just position your cursor on the square and drag it over the cells you want to apply the formula.

Consequently, now you have two columns of original text strings with spaces and trimmed texts without extra spaces.

Removing Leading Spaces Only using TRIM function

Occasionally, you may want to remove only the leading spaces and not the rest. In the following example, we have a few addresses with a double space between the different parts of the address. This is done to improve readability. But, there are also some leading spaces in the cells.

If we use the TRIM function on these addresses, it will remove all extra spaces, including the double spaces that we added to improve readability. However, you can try different formula combinations to remove only the leading spaces from the strings.

You can use TRIM function with LEFT, FIND and REPLACE functions to remove leading spaces:

=REPLACE(A1,1,FIND(LEFT(TRIM(A3),2),A1)-1,"")

The ‘FIND’ function would find the position of the first character in the address in cell A1. In the above example, in the first address, 2 is the first character which is in the fifth position (as there are 4 leading spaces before it). Then all the characters after the fifth position are extracted. REPLACE function would remove all the leading spaces by replacing them with extracted characters.

Removing Non-Breaking Spaces using the TRIM function

Unfortunately, TRIM function can’t delete all spaces, especially a non-breaking space, which can appear in Excel as CHAR(160) (See below example).

But by adding the SUBSTITUTE function to the TRIM formula, you can remove non-printable characters. Non-breaking space is also a non-printable character.

To remove a non-breaking space, you can use the following formula: