How to Remove Spaces in Excel (Two Useful Ways)

How to Remove Spaces in Excel

In today’s tutorial, we’re gonna go through two helpful ways how to remove spaces in Excel. The first one comes handy when you need to ‘tidy up’ numbers or continuous chunks of text. The second one is perfect for texts consisting of two or more words where you just need to remove the leading or trailing spaces, but you want to keep a space between the words.

Sounds interesting? Let’s get started then!

Would you rather watch this tutorial? Click the play button below!

The First Way How to Remove Spaces in Excel

Let’s start straight away with the first way to remove spaces and sort out the data here in this table.

If the data consists of numbers only or there are continuous chunks of text in the table, the best way’s to go for the option ‘Find and Replace’ which is a quick and effective help when it comes to removing spaces in a data set.

How to Remove Spaces in Excel - Find and Replace Option

Here’s how it works.

Go to the Home tab, click on ‘Find & Select’ and choose ‘Replace’.

How to Remove Spaces in Excel - Home Tab
How to Remove Spaces in Excel - Replace

Alternatively, you can press the hot keys Ctrl + H and the same window opens.

How to Remove Spaces in Excel - Ctr + H

Once done, there are two bits of information that needs to be entered in the two text fields.

How to Remove Spaces in Excel - two text fields

First we need to find each space in the data table. So in the field ‘Find what’, we need to specify the character we’re looking for, which is a space.

How to Remove Spaces in Excel - find what

Then we need to enter the character which is to replace the space. This bit of information will go in the text field ‘Replace with’. Since we want to remove the spaces, we’ll leave this field empty.

How to Remove Spaces in Excel - replace with

This is an instruction for Excel to look for each space and replace it with nothing, which basically means it will delete the spaces.

Now, once everything’s been set, click on ‘Replace All’ and all spaces have been removed as needed.

How to Remove Spaces in Excel - replace all
How to Remove Spaces in Excel - spaces removed

But let’s have a look at the second way.

The Second Way How to Remove Spaces in Excel (Using the TRIM Function)

We’ll change sheets and use this data table containing texts which consist of more than one word.

In this case, we need to remove only the leading and trailing spaces and redundant spaces between the words, but we want to keep a single space to separate two words.

How to Remove Spaces in Excel - remove only the leading and trailing spaces and redundant spaces

If we remove the spaces the same way as previously, Excel will remove each space throughout the table indiscriminately and the names and surnames will blend like this, which is what we definitely don’t want.

How to Remove Spaces in Excel - names and surnames blended

Let’s undo the changes and have a look at the other way to remove the spaces correctly.

For that, we’ll use the function ‘TRIM’.

How to Remove Spaces in Excel - trim function

Click into the cell C3, enter the equal sign and type in ‘TRIM’. Then use brackets to specify the cell in which you want to remove the spaces – in our case, it’s B3. Close the brackets, hit ‘Enter’ and that’s it!
All redundant spaces present in B3 have now been removed in C3

How to Remove Spaces in Excel - using trim function
How to Remove Spaces in Excel - redundand spaces been removed

The function can be copied by dragging the bottom right-hand corner of the cell down the column.

How to Remove Spaces in Excel - copied function

However, there’s one little thing to bear in mind.

If you copy the data from column C to column B, you’ll see the table empty. The reason behind this is very simple – Excel copies the function itself, not the values which are the results of the function.

How to Remove Spaces in Excel - empty table
How to Remove Spaces in Excel - excel copies the function itself

Go back and let’s have a look at how to copy the data correctly to see the values not the function in column B.

How to Copy the Data Correctly to See the Values not the Function

We begin – as before – by selecting the area to copy. Then we select ‘Copy’ through a right-click.

How to Remove Spaces in Excel - select Copy option

However, instead of regular pasting, we’ll click on ‘Paste’ up here at the top now and select ‘Paste Values’ from the options. This way, Excel will paste only the values which are the results of the function TRIM we entered a while ago.

How to Remove Spaces in Excel - select option Paste Values
How to Remove Spaces in Excel - values copied

By pasting values, the correct data will show in the original B column and we can simply get rid of the additional column C.

How to Remove Spaces in Excel - column C removed

Don’t miss out a great opportunity to learn:

If you found this tutorial helpful, give us a like and watch other tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.