How to remove spaces in Excel Data

posted in: Excel Tutorials | 1

How to remove spaces in Excel Data

excel tutorials for dummies

Remove spaces Excel Tutorial

In this tutorial we will outline how you can remove spaces from text or data in Excel. In some data sets, spaces can cause an issue for users. For instance a blank cell is not considered blank if it contains a space, which is not visually apparent.

Another example is where double barrelled terms contain a space between them but this is not desired by the user. For instance in our working example below, a term we use is Coca Cola, whereas you may wish the term to read CocaCola with no space. This can be important when combined with data validation purposes, and for a multitude of other reasons.

How to remove spaces in excel data

We have created a working example to outline how to remove all spaces in Excel from a range that you define. Please take note of the data set below which contains different 2 word phrases we wish to condense into a single word by removing the space between them. Rather than do this manually there is a very simple workaround.

remove spaces excel

To begin you first need to select the Excel data range for where you wish to remove spaces from. This can be a custom range that you define by highlighting the cell you wish, or it can be an entire column or row. In our example below we simply select a custom range as shown below.

excel remove spaces

Once you have selected the data range in excel you wish to remove spaces from, you will then need to navigate to the Home tab on the ribbon and then click Find and Select on the right hand side.  From there you will need to select Replace. (Excel Tip – A shortcut is Control + H)

remove spaces from text

This will bring up a dialogue box with 2 data entry fields, “Find What” and “Replace With”, in the top “Find What” box simply type in a single space and nothing else, leave the “Replace With” box blank.

remove all spaces

Once you have done the above step you will need to press the “Replace All” button. This will remove any spaces in the selected ranges you defined. This will give you a pop up dialogue box which will inform you how many replacements were made (ie how many spaces were removed) which also confirms your method has worked.

how to remove spaces in excel

 

This concludes our tutorial on how to remove spaces in Excel.

Excel Remove Spaces Video Tutorial

 

For similar tutorials and tips please visit the YouTube channel.

One Response

Leave a Reply