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 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:

 

STEP 1:  Enter the TRIM formula in cell C7.

=TRIM(

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

=TRIM(B7)

The spaces get removed with no issues:

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

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.

 

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):

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

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

 

STEP 1: Enter the SUBSTITUTE formula in cell D9.

=SUBSTITUTE(

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

=SUBSTITUTE(C9

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

=SUBSTITUTE(C9,CHAR(160),

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

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

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

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

 

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.

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.

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

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

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

All the spaces are now removed!

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:

• Clean Data Set for Pivot Table
• Top Excel Data Cleansing Techniques
• 5 Effective Ways to Split String in Excel for Better Data Management

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