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.

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.

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.

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

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

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

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.

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

• Secondly, press Enter to see the result.

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

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.

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.

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

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.

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.

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

Similar Readings

• How to Remove Spaces in a Cell in Excel (5 Methods)
• Remove Space in Excel before Numbers (3 Ways)
• How to Remove Blank Spaces in Excel (7 Ways)
• How to Remove Space in Excel after Text (6 Quick ways)

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.

• 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

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

• 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.

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.

• 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

• How to Remove Trailing Spaces in Excel (6 Easy Methods)
• Remove Space Before Text in Excel (4 Methods)
• How to Remove the Trailing Spaces in Excel (2 Easy Ways)
• Remove Tab Space from Excel (5 Easy Methods)
• How to Remove Space between Rows in Excel (5 Methods)