{"id":6490,"date":"2021-01-20T16:42:17","date_gmt":"2021-01-20T15:42:17","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=6490"},"modified":"2024-05-19T11:08:11","modified_gmt":"2024-05-19T09:08:11","slug":"how-to-remove-leading-and-trailing-spaces-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-remove-leading-and-trailing-spaces-in-excel\/","title":{"rendered":"How to Remove Leading and Trailing Spaces in Excel"},"content":{"rendered":"

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

So, it is extremely important to Excel remove trailing spaces!<\/h4>\n

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

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!<\/p>\n

<\/a>TRIM Function to Remove Extra Spaces<\/span><\/h3>\n

What does it do?<\/strong><\/em><\/p>\n

Removes unneeded spaces in your text, except single spaces in between words<\/p>\n

Formula breakdown:<\/strong><\/em><\/p>\n

=TRIM(text)<\/p>\n

What it means:<\/strong><\/em><\/p>\n

=TRIM(text that you want extra spaces to be removed)<\/p>\n

In the example below, you will see the text contains extra spaces<\/strong> at the beginning, middle, and even end of the sentence.<\/p>\n

Let’s remove the unwanted spaces and keep only a single space<\/strong> between words.<\/p>\n

Follow the step-by-step tutorial below on Excel remove trailing spaces and leading spaces and download this workbook to follow along:<\/p>\n

download excel workbook<\/i><\/strong><\/span>How-to-Clean-Leading-and-Trailing-Spaces.xlsx<\/span><\/a><\/div>\n

 <\/p>\n

STEP 1:<\/strong> \u00a0<\/span>Enter the TRIM formula in cell C7.<\/p>\n

=TRIM(<\/span><\/h3>\n

\"How<\/a><\/p>\n

STEP 2:<\/strong> \u00a0<\/span>Enter the argument required for this formula – text from which you want extra spaces to be removed.<\/p>\n

=TRIM(<\/span>B7)<\/span><\/h3>\n

\"How<\/a><\/p>\n

The spaces get removed with no issues:<\/p>\n

\"How<\/a><\/p>\n

STEP 3:<\/strong> \u00a0<\/span>Now, copy-paste\u00a0the formula below<\/strong> to the cell range<\/a> C7:C10.<\/p>\n

\"How<\/a><\/p>\n

That’s it!<\/p>\n

All the unwanted spaces are gone.<\/p>\n

 <\/p>\n

It is quite easy to Excel remove trailing spaces and leading spaces<\/strong> using the Trim function.<\/p>\n

In the example below, we have used the TRIM function on both cell D7 and cell D8.<\/p>\n

\"How<\/a> \u00a0\"How<\/a><\/p>\n

Since cell D8 has non-breaking spaces<\/strong>, the TRIM formula will not remove these spaces. <\/strong>The leading space is still there!<\/p>\n

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.<\/p>\n

 <\/p>\n

What are non-breaking spaces you ask?<\/strong><\/p>\n

Sometimes data downloaded as text uses non-breaking spaces for formatting purposes i.e. it prevents an automatic line break<\/a> in between these spaces.<\/p>\n

This is represented by CHAR(160)<\/strong>:<\/p>\n

\"How<\/a><\/p>\n\n

This looks like a pain! How do we clean these?<\/strong><\/p>\n

Let’s move forward and understand how to remove non-breaking spaces using SUBSTITUTE below!<\/p>\n

<\/a>Substitute Function to Remove <\/strong>Non-Breaking Spaces<\/strong><\/span><\/h3>\n
\n

We will use the SUBSTITUTE Formula<\/a>\u00a0<\/strong>to remove the non-breaking spaces depicted by\u00a0CHAR(160).<\/strong><\/p>\n<\/div>\n

The goal is to replace the non-breaking spaces with normal spaces.<\/p>\n

 <\/p>\n

STEP 1:<\/strong> <\/span>Enter the SUBSTITUTE formula<\/a><\/strong> in cell D9.<\/p>\n

=SUBSTITUTE(<\/span><\/h3>\n

\"How<\/a><\/p>\n

STEP 2:<\/strong> <\/span>Enter the first argument<\/strong> – the cell that contains the text you wish to substitute.<\/p>\n

=SUBSTITUTE(C9<\/span><\/span><\/h3>\n

\"How<\/a><\/p>\n

STEP 3:<\/strong> <\/span>Enter the second argument<\/strong> – the existing text you wish to substitute.<\/p>\n

=SUBSTITUTE(C9,CHAR(160),<\/span><\/span><\/h3>\n

\"How<\/a><\/p>\n

STEP 4:<\/strong> <\/span>Enter the third<\/strong> argument<\/strong> – the new text you wish to replace with.<\/p>\n

=SUBSTITUTE(C9,CHAR(160),<\/span>” “)<\/span><\/span><\/h3>\n

Note:<\/strong> CHAR(160) represent non-breaking spaces in a text.<\/em><\/p>\n

STEP 5:<\/strong><\/span>\u00a0Now that we do not have the non-breaking spaces anymore, let us use the\u00a0TRIM Formula<\/a><\/strong>!<\/p>\n

=TRIM(SUBSTITUTE(C9,\u00a0CHAR(160), ” “)<\/span>)<\/span><\/h3>\n

\"How<\/p>\n

Now all of your\u00a0leading and trailing spaces are cleaned up!<\/p>\n

\"How<\/p>\n

 <\/p>\n

<\/a>Find & Replace to remove all spaces<\/strong><\/span><\/h3>\n

You can use the find and replace feature<\/strong> 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.<\/p>\n

\"How<\/a><\/p>\n

To remove all space<\/a> characters from a cell, follow the steps below:<\/p>\n

STEP 1:<\/strong><\/span> Select the cell range from which you want to remove spaces.<\/p>\n

\"How<\/a><\/p>\n

STEP 2:<\/strong><\/span> Go to\u00a0\u00a0Home > Find & Select > Replace<\/strong> or Press Ctrl + H<\/strong> to open the Find & Replace dialog box.<\/p>\n

\"How<\/a><\/p>\n

STEP 3:<\/strong><\/span> Press the Space bar in the Find what<\/strong> field to insert a space and keep the Replace with field empty<\/strong>.<\/p>\n

\"How<\/a><\/p>\n

STEP 4:<\/strong><\/span> Click the Replace All button and then click OK.<\/p>\n

\"How<\/a><\/p>\n

All the spaces are now removed!<\/p>\n

\"How<\/a><\/p>\n

This is how you can Excel remove<\/a> trailing spaces, trailing spaces, and non-breaking spaces, too!<\/p>\n

 <\/p>\n

Conclusion<\/span><\/h3>\n

Using the Trim function<\/strong><\/a>, you have learned how to remove trailing spaces in Excel and leading spaces too.<\/p>\n

There can be non-breaking spaces in a text as well. To remove them, simply use Substitute Function<\/strong> and replace the non-breaking space with a normal space.<\/p>\n

Lastly, if you wish to remove all spaces in Excel the Find & Replace<\/strong> feature would be your best match!<\/p>\n

Give these methods a try and become a pro in cleaning data in MS Excel<\/a>!<\/p>\n

Further Learning:<\/strong><\/p>\n

\"How<\/a><\/p>\n