In Excel, you may sometimes find unwanted and extra spaces in your data. It might take a considerable amount of time to clean this data and make it perfect for further analysis.

So, it is extremely important to Excel remove trailing spaces!

Leading spaces (at the start of the text), Trailing spaces (at the end of the text) and Non-Breaking spaces (prevents line breaks from occurring at a particular point) usually get in the way when we want to perform operations in Excel.

Whether it leading, trailing or non-breaking spaces, we have a couple of ways how to easily clean them in Excel:

Let’s look at each one of these methods on how to remove leading spaces in Excel, trailing spaces as well as non-breaking spaces!

 

TRIM Function to Remove Extra Spaces

What does it do?

Removes unneeded spaces in your text, except single spaces in between words

Formula breakdown:

=TRIM(text)

What it means:

=TRIM(text that you want extra spaces to be removed)

In the example below, you will see the text contains extra spaces at the beginning, middle, and even end of the sentence.

Let’s remove the unwanted spaces and keep only a single space between words.

Follow the step-by-step tutorial below on Excel remove trailing spaces and leading spaces and download this workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1:  Enter the TRIM formula in cell C7.

=TRIM(

excel remove trailing spaces

STEP 2:  Enter the argument required for this formula – text from which you want extra spaces to be removed.

=TRIM(B7)

excel remove trailing spaces

The spaces get removed with no issues:

excel remove trailing spaces

STEP 3:  Now, copy-paste the formula below to the cell range C7:C10.

excel remove trailing spaces

That’s it!

All the unwanted spaces are gone.

 

It is quite easy to Excel remove trailing spaces and leading spaces using the Trim function.

In the example below, we have used the TRIM function on both cell D7 and cell D8.

excel remove trailing spaces  excel remove trailing spaces

Since cell D8 has non-breaking spaces, the TRIM formula will not remove these spaces. The leading space is still there!

The trim function can only remove normal spaces (ASCII character code 32). Non-breaking space has an ASCII character code 160 and it cannot be removed using the trim function.

 

What are non-breaking spaces you ask?

Sometimes data downloaded as text uses non-breaking spaces for formatting purposes i.e. it prevents an automatic line break in between these spaces.

This is represented by CHAR(160):

How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline

How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline

This looks like a pain! How do we clean these?

Let’s move forward and understand how to remove non-breaking spaces using SUBSTITUTE below!

Substitute Function to Remove Non-Breaking Spaces

Watch how to Excel remove trailing spaces on YouTube and give it a thumbs-up:

How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline

We will use the SUBSTITUTE Formula to remove the non-breaking spaces depicted by CHAR(160).

The goal is to replace the non-breaking spaces with normal spaces.

 

STEP 1: Enter the SUBSTITUTE formula in cell D9.

=SUBSTITUTE(

excel remove trailing spaces

STEP 2: Enter the first argument – the cell that contains the text you wish to substitute.

=SUBSTITUTE(C9

excel remove trailing spaces

STEP 3: Enter the second argument – the existing text you wish to substitute.

=SUBSTITUTE(C9,CHAR(160),

excel remove trailing spaces

STEP 4: Enter the third argument – the new text you wish to replace with.

=SUBSTITUTE(C9,CHAR(160),” “)

excel remove trailing spaces

Note: CHAR(160) represent non-breaking spaces in a text.

STEP 5: Now that we do not have the non-breaking spaces anymore, let us use the TRIM Formula!

=TRIM(SUBSTITUTE(C9, CHAR(160), ” “))

excel remove trailing spaces

Now all of your leading and trailing spaces are cleaned up!

excel remove trailing spaces

 

Find & Replace to remove all spaces

You can use the find and replace feature in Excel to amend data within seconds. In the example below, you have a list of few numbers and you wish to remove any spaces between these numbers.

excel remove trailing spaces

To remove all space characters from a cell, follow the steps below:

STEP 1: Select the cell range from which you want to remove spaces.

excel remove trailing spaces

STEP 2: Go to  Home > Find & Select > Replace or Press Ctrl + H to open the Find & Replace dialog box.

excel remove trailing spaces

STEP 3: Press the Space bar in the Find what field to insert a space and keep the Replace with field empty.

excel remove trailing spaces

STEP 4: Click the Replace All button and then click OK.

excel remove trailing spaces

All the spaces are now removed!

excel remove trailing spaces

This is how you can Excel remove trailing spaces, trailing spaces, and non-breaking spaces, too!

 

Conclusion

Using the Trim function, you have learned how to remove trailing spaces in Excel and leading spaces too.

There can be non-breaking spaces in a text as well. To remove them, simply use Substitute Function and replace the non-breaking space with a normal space.

Lastly, if you wish to remove all spaces in Excel the Find & Replace feature would be your best match!

Give these methods a try and become a pro in cleaning data in MS Excel!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline