Pinterest Pixel

How to Remove Leading and Trailing Spaces in Excel

In Excel, you may sometimes find unwanted and extra spaces in your data. It might take a... read more

Download Excel Workbook
Bryan
Posted on

Overview

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.

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 workbookHow-to-Clean-Leading-and-Trailing-Spaces.xlsx

 

STEP 1:  Enter the TRIM formula in cell C7.

=TRIM(

How to Remove Leading and Trailing Spaces in Excel

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

=TRIM(B7)

How to Remove Leading and Trailing Spaces in Excel

The spaces get removed with no issues:

How to Remove Leading and Trailing Spaces in Excel

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

How to Remove Leading and Trailing Spaces in Excel

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.

How to Remove Leading and Trailing Spaces in Excel  How to Remove Leading and Trailing Spaces in Excel

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

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

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(

How to Remove Leading and Trailing Spaces in Excel

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

=SUBSTITUTE(C9

How to Remove Leading and Trailing Spaces in Excel

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

=SUBSTITUTE(C9,CHAR(160),

How to Remove Leading and Trailing Spaces in Excel

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

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

How to Remove Leading and Trailing Spaces in Excel

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), ” “))

How to Remove Leading and Trailing Spaces in Excel

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

How to Remove Leading and Trailing Spaces in Excel

 

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.

How to Remove Leading and Trailing Spaces in Excel

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.

How to Remove Leading and Trailing Spaces in Excel

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

How to Remove Leading and Trailing Spaces in Excel

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

How to Remove Leading and Trailing Spaces in Excel

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

How to Remove Leading and Trailing Spaces in Excel

All the spaces are now removed!

How to Remove Leading and Trailing Spaces in Excel

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!

Further Learning:

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

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!