How to Remove Spaces in Excel

Have you ever wondered you keep searching data on Excel but it is not returning any values and many times you are trying to add columns or data but you are unable to do so? Well, these are some common problems we all have faced in our life. But do you know why it happens? This happens because of extra space(hidden) that remains in the text or number of your data.

By the end of this article, you'll know how to make your Excel data look neater and work better using simple functions and tools like TRIM, Find and Replace, and more.

How to Remove Spaces in Excel

Well, Excel gives you many ways to remove the spaces. We will learn about some of the simplest ways to remove the spaces in Excel that can increase our efficiency. So, let's understand some of the ways:

How to Remove Blank Spaces Using TRIM Function

To use the Trim function, we need to make a temporary column for the correct data at the end of the spreadsheet.

Step 1: Enter Data in the Excel Sheet

Let us take data in the Column Name and a temporary column called "Temp".

Step 2: Apply Formula and Select Cell to Remove Blank Spaces

Select the first cell of the temp column. Now go to the FORMULAS tab and then select the Text dropdown menu and click on TRIM. Then a dialogue box will pop up, now select the cell where you want to remove the blank spaces and then hit OK. 

Step 3: Remove Extra Spaces in Column Name

So, now the extra spaces in cell A2 get removed and it will be reflected in the new column Temp. Use the same function to remove all the extra spaces in the column Name. Afterward, just copy the column Temp paste all the data in the Name, and delete your temporary column.

Note: The TRIM function does not remove a single space between the words.

How to Remove Spaces Using SUBSTITUTE Function

Step 1: Enter Data in the Excel Sheet

Now, to use the formula, we need to make a temporary column for the correct data at the end of the spreadsheet. So we have taken a data and temporary column called "Temp" similar to the first case.

Step 2: Select a Cell and Apply the Substitute Formula

Again select the first cell of the Temp column then go to the FORMULAS tab and then select the Text dropdown menu and click on SUBSTITUTE. 

Step 3: Functional Argument Box Popped Up

Afterward, a dialogue box will appear. Now enter the cell where we need to remove spaces in the Text Field. Then put  " " in the 'Old_text' field and enter "" In the 'New_text' field and then press OK.

Step 4: Extra Blank Spaces Removed

So, now the extra spaces in cell A2 get removed and it will be reflected in the new column Temp. Use the same function to remove all the extra spaces in the column Name.

Step 5: Copy Temp, Paste in Name, and Delete Temporary Column

Afterward, just copy the column Temp paste all the data in the Name, and delete your temporary column.

How to Remove Extra Spaces Between Words Using Find and Replace

"Find and Replace" function helps in deleting Excess Spaces between words.

Note: This method will search for and replace all the spaces in the entire sheet or selection area. So make sure you don't remove the spaces you required.  

Step 1: Select a Column or set of columns from which you need to remove spaces. 

Step 2: Click "CTRL + F" or "CTRL + H " to open the find and replace dialogue box and click on replace.

Step 3: Now type  "space twice"  in Find what box and a "single space" in replace with box.

Step 4: Click on Replace All and press OK from the dialogue box.

Repeat step 3 until you find a Message alert "We couldn't find anything to replace". 

How to Remove Spaces Using CLEAN Function

While importing data from some other source, it comes along with extra space as well as some non-printing characters like vertical and horizontal tabs, etc.

The TRIM function can only help you to remove spaces, and not with other things. To delete Line Breaks and Non-Printing Characters the solution is to use the TRIM function with a combination of CLEAN functions. The Purpose of the CLEAN Function is to clean the data, and also it can delete any of the first 32 non-printing characters in the 7-bit ASCII set(values 0 through 31) including Line break.

Using Clean Function to Remove Spaces

When having loads on your data stored in an Excel sheet the clean function will help to remove it. This helps in maintaining the consistency of data and performing calculations easily on data reducing complexity.

Syntax: " =TRIM(CLEAN(Text)) "

Example:

To use the clean function follow these steps:

Step 1: Select Cells

Select the cells that you want to clean in the spreadsheet.

Step 2: Access the Data Tab, Click Text to Columns and Select Delimited

Go to the 'Data' tab and click on it, a dropdown menu will appear. Click on the 'Text to Columns' button and then select the 'Delimited' option.

Step 3: Select Space and Click on Finish

Make sure to select the 'Space' option as the delimiter and then click on the 'Finish' button.

Result:

How to remove all spaces between numbers

If we have data in the form of numbers where the digits (thousands, millions, billions) are separated with spaces. Excel will treat numbers as text and no math operation can be performed.

The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option.

Different Types of Spaces to Remove in Excel

Here are different types of spaces you can remove in Excel:

1. Leading Spaces: These are spaces before your text. For example, if a cell contains " Excel", using the TRIM function (=TRIM(A1)) can remove the leading space, resulting in "Excel".

2. Trailing Spaces: Spaces after your text, like "Excel ". Again, the TRIM function can clean this up, leaving you with just "Excel".

3. Double Spaces within Text: Sometimes, you'll find extra spaces between words, e.g., "Microsoft  Excel". The TRIM function is also effective here, reducing the double space to a single space: "Microsoft Excel".

4. Non-Breaking Spaces (NBSP): These are spaces that prevent an automatic line break at their position and might not be removed by the TRIM function. You can use the SUBSTITUTE function to tackle them:" =SUBSTITUTE(A1, CHAR(160), " ") ".

5. Fixed Width Spaces: Often used in formatting and might require a specific approach like using the SUBSTITUTE function with the appropriate character code for removal.

Conclusion

To remove blank spaces from the data in the Excel sheet which causes hindrance in performing calculations or makes data inconsistent and unorganized many methods can be used as discussed above in the article. You can use the find and replace method which not only removes spaces from text but also from numbers or the clean with stream function.

Applying all the above-mentioned methods is easy and saves you a lot of time in performing formulas, searching, and calculations saving the time.