How To Remove Spaces In Excel Using
The Function TRIM (Step-By-Step)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

When you import text into Excel, it often comes with extra spaces. And that can be very annoying.

Sometimes it can even prove problematic when you’re using that text in a function.

Fortunately, Excel has a very simple solution for that problem: the TRIM function!

We’ll be looking at what TRIM does and when you might use it in the rest of this post.

Read on to find out more…

Get your FREE exercise file

Want to practice using the TRIM function and see it in action?

We’ve created a free example workbook for just that purpose! Download it below and follow along with the rest of the article.

Download the FREE Exercise File

Download exercise file
Download free exercise file

Where do extra spaces come from?

If you haven’t encountered unwanted spaces before, you don’t understand how frustrating they can be.

When you copy and paste data from another program—like Word, Access, or Chrome—you can unintentionally get an extra space in there.

Whether that’s due to a software quirk or because you highlighted the space doesn’t matter. It’s there, and it’s irritating.

extra-spaces

And if you’re using any text-related functions, it can cause problems.

If you have text with a trailing space and you concatenate it with text that has a leading space, you end up with two spaces between the words. If you do a character count using LEN, the spaces are counted and can throw off your calculations.

No matter why you have an extra space, you want to get rid of it. And scrolling through thousands of cells looking for extra spaces takes way too long.

Enter the TRIM function.

Why don’t numbers get extra spaces?

You’ll notice that we’re only working with text in these examples.

That’s because Excel automatically trims the extra spaces around numbers. If you have numbers formatted as text, though, TRIM will work with them, too.

Banishing extra spaces with TRIM

To see how TRIM works, open up the example workbook. On the first sheet, there’s a column of data—each cell contains at least one extra space.

It’s not always easy to tell, is it? Trailing spaces are especially hard to pick out.

But with TRIM, you don’t need to find those spaces. You can get rid of all of them at once.

To show you how effective TRIM is, we first need to do character counts on our current cells. Click into C2 and type the following formula:

=LEN(A2)

When you hit Enter, you’ll see the number of characters in cell A2.

len-cell

That’s not very informative on its own (though you might notice that “Jeff Davis” should only have ten characters).

Click into E2 and type this formula:

=TRIM(A2)

Then hit Enter.

There’s only one possible argument available for the TRIM function. It can either be a cell reference or, as we’ll see in a moment, written text.

Kasper Langmann, Co-founder of Spreadsheeto
trim-cell

Looks the same, right? Click into G2 and use the LEN function to get a character count of the new cell, though, and you’ll see that it isn’t:

len-trimmed-cell

The character count has decreased by two, because there were two trailing spaces on the text in the first cell.

Use the fill handle to find the length of each text cell in the first column. Then run TRIM on those cells and measure again to see what happens!

Kasper Langmann, Co-founder of Spreadsheeto

It’s worth noting that you can also enter text directly into the trim function. Try this:

=TRIM(” too many   spaces”)

trim-in-action

As you can see, it immediately strips out the leading space and the extra spaces between “many” and “spaces.”

You probably won’t use text as an argument as you will a cell reference, but it’s good to know anyway.

Pro tip: working with trimmed text

Keep in mind that when you run TRIM on another cell, you’ll always have a formula in the second cell.

If you want to have only the value of the trimmed text in a cell, you’ll need to copy and paste the value only.

To do that, select the cell and hit Ctrl + C to copy it.

Then right-click in the new location and select “Paste values only.”

paste-values-only

Now you’ll have the trimmed text with no formula to worry about.

This also ensures that your trimmed text doesn’t change if you change the text in the original cell.

No more annoying extra spaces

No matter how you’re getting extra spaces in your spreadsheets, they’re annoying.

But with TRIM, you can get rid of them quickly, easily, and without scrolling through hundreds of rows.

Just remember to copy and paste the values if you want to keep them permanent, and get on with the rest of your work!

2019-03-28T15:30:30+00:00