How to remove spaces in Excel Data
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.
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.
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)
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.
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.
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.