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 you all have faced in your 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.

So this article will guide us: 

How to Remove Spaces in Excel

Well, Excel gives you many ways to remove the spaces. In this article, 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 in Excel – Leading, Trailing, Between Words

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 By Using a Formula

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.

Using Find and Replace to Remove Extra Spaces Between Words

It 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 Delete Line Breaks and Non-Printing Characters

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. The solution to this problem 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: TRIM(CLEAN(A2))

To use the clean function follow these steps:

Step 1: Select Cells

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

Select Cells

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.

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

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.

Select Space and Click on Finish

Result:

Data is cleaned

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

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.

FAQs

Why is TRIM not removing spaces in Excel?

Excel TRIM can only delete value 32 in the 7-bit ASCII system, which is the space character.

How to remove spaces in digits?

Step 1: Select the data in which you want to remove spaces.

Step 2: Click “CTRL+H ” to open the “find and replace “dialogue box.

Step 3: Enter a ” ” (single space) in the find what text box and leave the Replace with empty.

Step 4: Now select the “ Replace all” dialogue box. This will remove all the spaces.

How do you trim blank spaces in Excel?

1. select the cell or range

2. Press Enter ‘=TRIM(cell reference)’ in the formula bar

How do you put spaces in text in Excel?

1. Right-click in the cell

2. Click Format cells

3. On the Alignment tab, change Vertical to Justify.

4. Click OK.

How do I remove spaces and dots in Excel?

Click file>option>Advance> uncheck tAutomatically insert a decimal point