How to Remove Spaces in Excel

When you import data from external sources and web pages, you are likely to find a lot of unwanted spaces. Here's how to remove them.

Excel is an incredibly powerful data analysis tool that continues to be the go-to for every individual even after 30+ years of its invention. Mastering some Excel functions is a good idea, as it can help you maximize your productivity. These also come in extremely handy when you want to do away with minor glitches like removing extra spaces and all.

Sometimes, when you paste data from an external source (web pages or text reports), you get extra spaces along with important data. Your table becomes disorderly and difficult to use.

How to Delete Spaces in Excel

There are three ways to remove extra spaces in Excel. You can use the Find & Replace option, the Trim formula, and the Trim spaces tool. Let's take a look at how to get it done.

Using Find & Replace Option

This is the easiest way to remove spaces in Excel, but you can only use it to delete spaces between words. If there are leading and trailing spaces, they will be reduced to 1 but will not be removed entirely.

1. Select one or several columns from which you'd like to delete extra spaces.
2. Click on Find & Replace or press CTRL+H.
3. Press the space bar twice in the Find what box, and press the space bar once in the Replace with box.

4. Click on Replace All and press OK to save your changes.

   
5. Repeat step 4 till you see the message, “We couldn't find anything to replace.”

RELATED: How to Use the Find and Replace Search Feature in Excel

Using the Trim Formula

If you're looking for an Excel formula to remove spaces, this is the one. Though it's a little time-consuming, it's efficient and works best for trailing and leading spaces.

1. Start by adding the Helper column to the end of your data, and name it “Trim.”

2. In the first cell of the Helper column, enter the Excel formula to remove spaces =TRIM (A2).

   
3. Copy the formula across all other cells as required.
4. Now, replace the original column with the one that has clean data. You can select all cells in the Helper column, press CTRL+C to copy them to the clipboard.
5. Once done, pick the first cell in the Helper column, press Shift+F10, and paste the same by pressing CTRL+V.

6. Remove the Helper column, and you'd have removed all extra spaces.

   

RELATED: How to Use the TRIM Function in Excel

Using the Trim Spaces Tool

The Trim Spaces add-in is the best option to remove extra spaces in Excel. That's because it clears all unwanted spaces including leading and trailing spaces, excess blanks between words, non-breaking spaces, line breaks, non-printing symbols, and does so in three clicks.

1. To start, download and install a trial version of Ultimate Suite for Excel.
2. Select the range in your table, or press CTRL+A to select all cells.

3. Next, go to the Ablebits Data tab that's right next to the Data tab. Click on the Trim Spaces icon.

   
4. You'll see the add-in's pane open on the left side of your worksheet. Select the needed checkboxes and click the Trim button to have a perfectly cleaned table.

How to Remove Spaces Between Numbers

The methods mentioned above only work for spaces between words in most cases. To remove extra spaces in Excel between numbers, Find & Replace works best again. When you have a lot of digits separated by spaces, Excel views them as text.

1. Open the Find & Replace dialog box.
2. Type a single space in the Find what field and keep the Replace with tab empty.
3. Next, click on Replace All and press OK to confirm your changes. And voilà! All extra spaces will be removed. Enjoy a perfectly clean spreadsheet.

Removing Extra Spaces in Excel Is Easy

Using the TRIM formula can be somewhat time-consuming, especially when you're working on large worksheets. That's why we recommend using the Trim Spaces tool. It is the most efficient and gets your work done in less than 3 clicks.

For all your minor needs, the Find & Replace option should do more than just fine. No matter which option you choose, removing extra spaces in Excel is ridiculously easy.

About The Author