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.

Download Workbook

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.

TRIM(<string to be trimmed>)

How to remove spaces in Excel 01

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.

How to remove spaces in Excel 02

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.

=SUBSTITUTE(<string with spaces>,” “,””)

How to remove spaces in Excel 03

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

How to remove spaces in Excel 04

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.

=SUBSTITUTE(<string with non-braking space>,CHAR(160),””)

How to remove spaces in Excel 05

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;

  1. Open the Find & Replace dialog by pressing Ctrl + H,
  2. Enter a space character into the Find What box
  3. Leave the Replace with box empty
  4. (Optional) click <<<Options button and set Within as Workbook to remove all spaces in the workbook
  5. 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.