How to Remove Leading and Trailing Spaces in Excel

Sometimes data from external sources have leading and trailing spaces when pasted into Excel.

Imagine yourself manually deleting these spaces before importing them into your system 😿

Tiring and tedious isn’t it? Having to do this repeatedly for thousand of lines just to get clean data.

Luckily, Microsoft Excel has your back! It can remove unwanted spaces with minimal effort 😊

And it can be done in just a few minutes!

Let’s dive in 🤿

If you want to tag along, download the practice workbook here.

Remove extra spaces before, after, and between text and numbers

We always want clean data for our Excel files, right?

Especially if we are importing files from one system to another. And unfortunately, leading and trailing spaces will ruin this process 😢

To prevent this from happening, we need to clean the data first using the TRIM function.

The TRIM function removes spaces from a text string except for a single space between words.

TRIM has only one argument, which is the TEXT.

It is the text from which you want spaces removed.

However, it cannot completely remove spaces between words and numbers 😞

But sometimes that is what we want for most cases, right?

Now, let’s use the Trim function so that I can show you what I mean 😉

1. Using the sample workbook, select cell E2.

This is where we are going to type in the TRIM formula

2. In Cell E2, type in =TRIM(

3. Add cell C2 and a close parenthesis “)” on the Trim formula.

Cell C2 contains the full name information. And it will be our data for the TEXT argument. And as you can see, cell C2 has multiple spaces in it.

4. Press Enter.

As shown in the above formula, the leading spaces have been removed.

The in-between spaces have been reduced as well and now, only a single space remains.

But did you know that our sample, Abigail Howard, has trailing spaces?

It is not obvious but the TRIM function was able to remove that double space as well 😎

Remove ALL spaces before, after, and between text and numbers

But what if you want to remove all the spaces completely?

Including the spaces between words along with trailing spaces and leading spaces.

Sadly, the TRIM function will not be able to help in this case but do not fret my friend!

Excel still has the SUBSTITUTE function 😲

SUBSTITUTE function replaces existing text with new text in a text string.

It has three mandatory arguments and one optional argument:

• TEXT: the reference to a cell containing text in which you want to substitute characters
• Old_text: the original data to replace
• New_text: the text data to replace Old_text with
• Instance_num: specifies which occurrence of Old_text to replace. If omitted, every instance of Old_text is replaced

Let’s use the sample workbook again to demonstrate how to use it.

Refer to cell D2

As shown above, this data has extra spaces all over it!

Trailing spaces, leading spaces, two in-between spaces – a total mess 😨

But stay with me and we can clean this data together by removing all that spaces.

Let’s start!

As mentioned, we will be using the SUBSTITUTE function.

1. Go to Cell F2

2. Type in =SUBSTITUTE(

3. Insert Cell D2 and a comma: D2,

4. Next, add ” ” and a comma: ” “,

Why ” ” you ask?

It is simply because ” ” represents the space character in Excel 🤯

5. Lastly, add “” and a close parenthesis: “”)

I can feel you asking again, why “”?

Because “” represents a null value or nothing in Excel 😉

6. Press Enter

As you can see, all of the spaces have been removed!

But how does that actually work?

Let me explain it to you 😁

We used all three mandatory arguments, right?

1. TEXT: Cell D2
2. Old_text: ” ” (the space character)
3. New_text: “” (the null value)

What Excel did is it looked for all the space characters on the data (as set on the Old_text argument).

Then it replaced those with the null value (as set on the New_text argument).

Pretty neat, isn’t it?

Removing spaces is now a piece of cake for you 🍰

Remove non-printable characters and other codes

TRIM function and SUBSTITUTE function are very useful in removing spaces.

But what if we want to remove other stuff aside from spaces in Excel?

Did you know that we can also get non-printable characters when pasting data from an external source?

This usually happens if we copy data from web pages.

Sometimes, there are even line breaks that we will not notice until we select the cell!

And not noticing them early will surely affect your outputs 😭

But how do we deal with those?

Even TRIM and SUBSTITUTE functions cannot help with that.

Those are special character codes. And said Excel functions were not designed for that.

But don’t you worry my friend as Excel still has your back 😎

This time, we will be using the CLEAN function 🧹

The CLEAN function removes all non-printable characters from text.

It has only one argument which is the TEXT.

TEXT is any data from which you want to remove non-printable characters.

Before we start, let us revisit the sample workbook.

I will tell you something that you might not have noticed beforehand 🙀

If you select Cell C2, you will immediately see that there is a line break in it!

Sadly, our TRIM function was not able to remove this line break.

You can verify this by copying cell E2.

This is the cell we used for the TRIM formula.

Then use paste special, paste as values on the same cell.

Now, let’s get down to business!

We are going to clean this data using the CLEAN function 😏

1. Using the sample workbook provided, select Cell G2

2. Type in =CLEAN(

3. Add cell E2 next and complete the formula using a close parenthesis: “E2)“

We used the trimmed values from Cell E2 as our reference.

This is to make sure that all extra spaces have been removed as well.

4. Press Enter

They are completely identical at first glance right?

But don’t let your eyes fool you!

Try to copy and paste special cell G2.

You will then see that it no longer has the line break 😳

Cell E2:

Cell G2:

That’s all – Now what?

I commend you for making it this far! It was fun, right?

Learning how to remove spaces in Excel is a great skill especially when you need to deal with a huge set of data. Or if your data is usually extracted from web pages.

Using space is part of our daily lives but it can be annoying sometimes 😂

May it be a single space or multiple, if it did not fit right into our requirements, then we have to deal with it 🧐

But did you know that aside from TRIM, you can use other Excel features to remove extra spaces?

Take FIND and REPLACE as an example!

They are both powerful tools and can provide the same result as TRIM and SUBSTITUTE! Or maybe even more!

And you know what? I am offering them for free if you subscribe to my 30-minute email course 📧

Other resources

Since we are on the matter of cleaning and manipulating data, how about I offer you another article?

The CONCATENATE function for example 😎

Once you have cleaned your data, you can combine them using CONCATENATE!

Or perhaps, after cleaning the data you need to create a dropdown out of them? Then this Data Validation article is for you 😊