If you are working with data from different sources, you've more than likely came across unwanted spaces in the data. In this guide, we’re going to show you how to remove spaces in Excel.
There are mainly two ways you can use to remove spaces in Excel: Using formulas, and using the Find & Replace dialog. Let’s take a look at both methods.
Removing spaces by formulas
Trimming spaces in Excel
If are trying to get rid of extra spaces before or after strings, or in a case like multiple spaces between words, there is a function that does exactly this: TRIM
You only need to supply the string as an argument.
For example; =TRIM(" Ready to form Voltron! ") returns "Ready to form Voltron!”.
If you want to remove the formulas, first copy them and paste them on top of the original values as text.
This action will overwrite the original values with their trimmed counterparts.
Removing Spaces
If your aim is to remove all spaces from a cell, you can use the SUBSTITUTE function which can replace spaces with the character(s) you want. To remove all spaces, enter an empty string (or two, depending on your data) as the new_text argument.
Alternative Method
If you've tried both methods and didn't have any luck, this might mean that your string data contains characters that don't have a visual reference, and are used to control peripherals, such as printers. Typically, this is either a new line, return carriage, and non-breaking space (nbsp) characters. To remove new line and return carriage characters, see this page: How to Remove a Line Break in Excel
To remove non-breaking space, we will be following a similar approach. Since you can’t really type in these characters, we will use the CHAR function to populate the character for us.
The char function simply returns the character by a given ASCII code. The non-breaking space character’s code is 160. We can use the CHAR function in the SUBSTITUTE function to change the character with an empty string.
Find and Replace
You can use the Find and Replace feature to mimic the SUBSTITUE function capabilities. The tricky part is determining which character you need to enter into the Find what box. Unfortunately, trimming doesn't work with this method.
Remove spaces
To remove spaces from all instances in the cell;
- Open the Find & Replace dialog by pressing Ctrl + H,
- Enter a space character into the Find What box
- Leave the Replace with box empty
- (Optional) click <<<Options button and set Within as Workbook to remove all spaces in the workbook
- Click Replace All
Remove Non-breaking Space
The process is essentially the same as in the previous method. However, entering non-breaking spaces can be tricky. You need to type in "0160" on your numpad (not with the number above letters) while pressing Alt (Alt Gr) button.
