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.

**Table of Contents**hide

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

`=TRIM(B5)`

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.

`=SUBSTITUTE(B5,“ ”,“”)`

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

`=TRIM(SUBSTITUTE(B5,CHAR(160),CHAR(32)))`

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

`=MID(B5,FIND(MID(TRIM(B5),1,1),B5),LEN(B5))`

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.

`=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.

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

Thanks for your feedback, Bhat.

Best regards