How to Remove Spaces in Excel with Formula (5 Quick Ways)

In this article, we will learn to remove spaces in excel with the formula. Spaces create many problems when we try to execute any formula in the excel worksheet. Sometimes, when we copy the data and paste it into our excel sheet, extra spaces may occur unintentionally. It can produce incorrect results or errors. We will now demonstrate some methods to overcome this problem.


Download Practice Book

Download the practice book.


5 Ways to Remove Spaces with Excel Formula

1. Use of Trim Formula to Remove Spaces in Excel

Excel has a built-in formula that removes spaces from texts. It is the Trim formula. We will use a dataset of two columns to explain this method. These are Employee & ID Number. We will use the same dataset in all methods in this article.

Use of Trim Formula to Remove Spaces in Excel

Follow the steps below to learn this method.

STEPS:

  • In the beginning, we need to create a helper column. We named it ‘TRIM’ in our dataset.
  • Now, select Cell D5 and type the formula in the helper column.
=TRIM(B5)

Use of Trim Formula to Remove Spaces in Excel

Here, after typing the function, we need to select the cell from where we need to remove spaces.

  • Next, hit Enter to see the result.

Use of Trim Formula to Remove Spaces in Excel

  • Then, use the Fill Handle to see results in all cells.

Use of Trim Formula to Remove Spaces in Excel

  • After that, select Cell D5 and copy it.
  • Now, paste only the value in Cell B5.

Use of Trim Formula to Remove Spaces in Excel

  • Finally, after doing ‘Copy & Paste’ in all cells, delete the helper column.

Use of Trim Formula to Remove Spaces in Excel

Read More: How to Remove Spaces in Excel: With Formula, VBA & Power Query


2. Remove All Spaces Using Excel SUBSTITUTE Function

We can also delete spaces with the help of the Substitute function. It will remove all the spaces from the desired cell.

Pay attention to the steps to know more.

STEPS:

  • Firstly, create a Helper Column & type the formula.
=SUBSTITUTE(B5,“ ”,“”)

Remove All Spaces Using Excel SUBSTITUTE function

Here, this formula will replace spaces (second argument) with the empty string (third argument).

  • Secondly, press Enter to see the result.

Remove All Spaces Using Excel SUBSTITUTE function

  • Now, use the Fill Handle to see results in the Helper Column.

Remove All Spaces Using Excel SUBSTITUTE function

Here, we can see there is no space between the first name and the last name of the employees. We can certainly fix this problem by using the TRIM function at the beginning of the SUBSTITUTE function.

  • Put the formula in Cell D5.
=TRIM(SUBSTITUTE(B5,CHAR(160),CHAR(32)))

Remove All Spaces Using Excel SUBSTITUTE function

Here, the SUBSTITUTE function replaces the non-breaking spaces, CHAR(160) with normal spaces, CHAR(32). The TRIM function removes the extra spaces here. We need to add it in front of the SUBSTITUTE function.

  • Hit Enter to see the result.

Remove All Spaces Using Excel SUBSTITUTE function

  • Finally, use the Fill Handle for the rest of the cells.

Remove All Spaces Using Excel SUBSTITUTE function


3. Excel Formula with MID Function to Remove Leading Spaces

The MID function helps us to remove the leading spaces from a cell. It does not remove the extra spaces between texts. We will use the previous dataset again.

Observe the steps below to know about this method.

STEPS:

  • Create a Helper Column at first.
  • Now, type the formula in Cell D5.
=MID(B5,FIND(MID(TRIM(B5),1,1),B5),LEN(B5))

Excel Formula with MID function to Remove Leading Spaces

This formula will find the text and its length at first. The FIND function will return the position of the text string as a number and the LEN function will count the length of Cell B5. Then, it will trim the leading spaces from the text.

  • Next, press Enter. You can see in Cell D5 that there is no leading space. But it has spaces between texts.

Excel Formula with MID function to Remove Leading Spaces

  • Finally, drag the Fill Handle down to see results in the Helper Column.

Excel Formula with MID function to Remove Leading Spaces


Similar Readings


4. Apply VBA to Remove Extra Spaces in Excel

VBA also gives us the opportunity to eliminate extra spaces in excel. It can remove the spaces from the start and also from the end. But it will not be able to remove spaces between texts.

Follow the steps for this technique.

STEPS:

  • In the first place, go to the Developer tab and select Visual Basic.

Apply VBA to Remove Extra Spaces in Excel

  • Next, go to Insert in the Visual Basic window and then select Module.
  • Type the code in the Module and save it.
Sub Remove_Spaces()
Dim x As Range
Dim Cells As Range
Dim TCell As String
Set x = Selection
For Each Cells In x
TCell = Cells.Value
TCell = Trim(TCell)
Cells.Value = TCell
Next Cells
End Sub

Apply VBA to Remove Extra Spaces in Excel

  • After that, select the range of cells where you want to apply the VBA Here, we have selected Cell B5 to Cell B9.

Apply VBA to Remove Extra Spaces in Excel

  • Then, select Macros from the Developer.

  • Furthermore, select Run from the Macro.

  • Finally, you will see results like the picture below.


5. Insert Excel Formula to Delete Spaces between Numbers

Sometimes, we need to clean spaces between numbers. In this section, we will show how we can remove spaces between numbers. We will use the same dataset here. But, we will have spaces in the ID Number column this time.

Insert Excel Formula to Delete Spaces between Numbers

Follow the steps below.

STEPS:

  • Firstly, create an extra column. The Helper Column is the extra column here.
  • Secondly, select Cell D5 and enter the formula.
=SUBSTITUTE(C5," ","")

  • Thirdly, press Enter and use the Fill Handle in the Helper Column.

  • Alternatively, you can do this with the ‘Find & Replace’ Select the range of cells from where you want to remove spaces.

  • Next, press Ctrl + H from the keyboard. The ‘Find And Replace’ window will occur.
  • Press the Space bar in the ‘Find what’ section and keep the ‘Replace with’ section empty.

  • Finally, select Replace All to see the results.

Read More: How to Remove Space after Number in Excel (6 Easy Ways)


Things to Remember

We have discussed some easy methods about how to trim spaces in excel. There is an important thing we need to remember while we are using these methods. We must create an extra column at first for Method-1,2 & 3. After performing the steps, we need to replace the main data with the trimmed data. We can do this using Copy & Paste. Make sure to paste only values. This procedure is shown in Method-1.


Conclusion

We have described 5 methods to erase spaces from our excel worksheet. These are mainly formula-based methods. You can also use the ‘Find & Replace’ option which is discussed in the last method. Furthermore, I hope these methods will help you to solve your problem completely. Last of all, if you have any queries or suggestions, feel free to comment below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

2 Comments
  1. Good explanation on using excel command to make the work easy.Thanks

Leave a reply

ExcelDemy
Logo