How to Remove Spaces in Excel? 3 Easy Methods

(Note: This guide on how to remove spaces in Excel is suitable for all Excel versions including Office 365)

Dealing with large data in Excel can be quite difficult. Especially, when you copy or import huge data from any database, dealing with them can be a bit complicated. Sometimes, various functions and formulae might be needed to operate the values. 

One such example is unwanted spaces. Extra Spaces can be hard to deal with. Since extra spaces are also counted as characters, they can be a menace when you want to search through the huge data. In the case of numbers, some functions throw an error because space is treated as an additional character.

The spaces can be leading, trailing, or occurring in between words or numbers. Sometimes, extra spaces appear along with some unprintable characters.

Leading spaces and the spaces in-between values can be seen with a naked eye, but they too can be difficult to spot when you’re dealing with large data. Trailing spaces cannot be easily found. So, how do you remove these extra spaces? 

Don’t worry. In this guide, I will tell you 3 easy ways to remove spaces in Excel.

You’ll Learn:

• How to Remove Spaces in Excel Using TRIM Function?
• How to Remove Spaces in Excel Using Find and Replace?
• How to Remove Spaces in ExcelUsing SUBSTITUTE Function?

Watch this short video on How to Remove Spaces in Excel

Related:

The Excel SMALL Function – 3 Best Examples

How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide

How to Use Excel COUNTIFS: The Best Guide

1. How to Remove Spaces in Excel Using TRIM Function?

Excel has a lot of functions and formulas to help you perform certain operations. 

To remove unwanted spaces between texts you can use the TRIM function.

This function removes the excess leading, trailing, and spaces in-between values. However, there will exist a space that separates the two texts. You will need an empty column for the trimmed values. 

Consider an example that contains different combinations of leading, trailing, and spaces in-between.

To trim the extra spaces for the values, enter the function =TRIM(cell_number) in the destination cell or the formula bar. Here, cell_number denotes the place of the untrimmed text. 

In the above example, the untrimmed value lies in cell A3, so enter the formula =TRIM(A3) in the destination cell or the formula bar.  

This gives you the trimmed value without any leading, in-between, or trailing spaces.

Use the drag handle to apply the formula to other existing cells with untrimmed spaces.

This creates the trimmed values onto the helper column. You can replace the untrimmed values by copying and pasting the trimmed values to the destination.

Copy the trimmed values by right-clicking on the selected column and selecting cut/copy. While pasting, use the paste special and paste the trimmed spaces as values.

Note: In some cases, along with spaces, there might be additional non-printable characters. The TRIM function only removes the spaces, the CLEAN function removes any non-printable characters in the text. By using a combination of them you can remove both the spaces and the non-printable characters.  

Also Read:

How to Add Error Bars in Excel? 7 Best Methods

How to Superscript in Excel? (9 Best Methods)

Excel Goal Seek—the Easiest Guide (3 Examples)

2. How to Remove Spaces in Excel Using Find and Replace?

This is another method to remove extra spaces between words or numbers. This method works on the principle of finding the number of spaces and replacing it. However, this method leaves one space with leading, trailing, or in-between space. This method is very effective when the untrimmed values are more spread out over the spreadsheet.

One advantage of this method is that it replaces the values in the same place. You don’t have to create an additional empty column for the trimmed values. 

To trim the values, select untrimmed values.

Navigate to Home > Editing > Find & Select, and click on Replace. Or, simply press Ctrl+H. This opens up a Find and Replace dialog box.

Enter double-space (“  “) in the Find what: text box and single space (“ “) in the Replace with: text box.

Click Replace All.

This pops up a notification showing replacements made for a certain number of places. Click OK.

Again, click on replace all until you get a “We couldn’t find anything to replace” notification shows up.

Note: This method is especially very useful when removing spaces from numbers.

In some cases, large numbers can be separated by spaces. When performing any functions or while searching for numbers, the spaces become an obstacle.

Removing space from numbers is similar to the method of removing spaces from text. 

Select the untrimmed values. Then, click on Ctrl + H.

The Find and Replace dialog box opens. Enter a single space (“ “) in the Find what: text box, and leave the Replace with: text box empty.

Click on Replace All. This gives you the number trimmed of all the spaces.

This method can also be used to remove all the spaces from texts.

3. How to Remove Spaces in Excel Using SUBSTITUTE Function?

If you want to remove all the spaces associated with numbers or text, this method is the go-to. By using a simple formula, you can remove all the spaces from your data.

This method works on the principle of replacing the space with an empty string.

To trim the values of unwanted spaces, enter the formula =SUBSTITUTE(A22,” “,””) in the destination cell or the formula bar. Here, A1 is the cell that contains the untrimmed values. That is, the space (“ “) is replaced by an empty string value (“”).

Suggested Reads:

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Extract an Excel Substring? – 6 Best Methods

How to Split Cells in Excel? 3 Easy Methods

Closing Thoughts

Removing unnecessary spaces helps you perform Excel functions and easily search through large data. This also makes your data look more pleasing to the eye.

In this article, we have seen 3 methods to remove leading, trailing, or spaces in-between. Each method has its advantages. Choose the method that suits your preference.

If you need more high-quality Excel guides, please check out our free Excel resources center.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!