Buy Now
Remove-Trailing-Spaces

Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps

Jul 29, 2022

In Microsoft Excel, there might be times when you get left with unwanted leading and trailing spaces in your worksheet. If you opt to clean up these unwanted characters manually, it can take a long time to clear a trailing space and remove extra spaces without using the Excel trim function.

But what if I told you that you could remove leading or trailing spaces in Excel quickly and easily?

That's right; excess spaces are gone in the blink of an eye!

Leading spaces are spaces that appear at the start of your text string, and trailing spaces that appear at the end of your text data. They often get in the way of what you are trying to achieve in Microsoft Excel.

What extra spaces you come up against, leading and trailing spaces can be removed in a couple of different ways.

In this article, we will talk about how you can remove spaces in Excel and never have extra spaces or line breaks get in your way again.

TRIM Function to Remove Extra Spaces

The TRIM function is an excellent way to remove trailing spaces in your text, apart from single spaces in between words.

Here is what the TRIM function formula is to remove spaces:

=TRIM(text)

Let's break that down in a little bit more detail:

The =TRIM aspect highlights the text you want the extra spaces to be removed from.

It is really quick and easy to remove leading and trailing spaces with the TRIM function.

However, the TRIM function is not the best option for removing non-breaking spaces in Excel.

Non Breaking spaces are sometimes included in data downloaded as text for formatting purposes, as it stops an automatic line break from occurring.

For non-breaking spaces in excel, we need to use a different function to remove leading spaces.

Substitute Function to Remove Non-Breaking Spaces

To remove non-breaking extra spaces, we will need to use the SUBSTITUTE function.

Here's how to replace non-breaking spaces with normal spaces:

  1. Input the SUBSTITUTE formula in your chosen cell

=SUBSTITUTE(

  1. Input your first argument, which is the cell that contains your text that needs to be substituted.

=SUBSTITUTE(XX

  1. Input the second argument, which is the existing text you want to substitute.

=SUBSTITUTE(xx,CHAR(xxx),

  1. Input the third argument, which is the new text you want to replace it with.

=SUBSTITUTE(XX,CHAR(xxx),"")

  1. Now you have removed each non-breaking space and replaced all the extra spaces with normal spaces; we can use the TRIM function.

Find & Replace to Remove All Spaces

Those options work great for removing only a single space or a couple of consecutive spaces, but what about when you want to remove all the spaces in Excel?

This is where the Find and Replace to Remove every space character in your worksheet.

  1. Choose the cell range from which you want to remove all spaces.

  2. Head to Home, Find & Select, Replace, and this will open the Find & Replace dialog box.

  3. Hit the space bar in the Find section to insert a space and leave the Replace with field blank. That way, all space characters will be automatically highlighted.

  4. Select the Replace All button and hit OK, and you will remove spaces from the selected area.

Removing Leading and Trailing Spaces: Summary and Key Takeaways

Now you have various ways to remove a leading space or extra spaces in Excel! You can even remove non-breaking extra spaces and replace them with a normal space in Excel!

Remember to practice using your TRIM function and SUBSTITUTE function to improve your skillset, and for removing spaces across your entire worksheet, don't forget to use the Find & Replace feature.

Related Articles

Excel Pivot Table Training: Everything You Need to Know

What is a Sunburst Chart and When to Use a Sunburst Chart in Excel

How to Calculate Confidence Interval in Excel: Easy to Follow Steps

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.