Spaces can occur for different reasons in a worksheet. There may be mistakes during data input or data import from different sources. Manually removing those spaces may be cumbersome and may require time. In this article, we will show you quick ways to **remove space in Excel** after text. These ways will be handy when we are to clean data.

Suppose, we have a list of names with unwanted spaces after text. We want to remove those spaces to perform further operations. We will do it using 6 different methods below. For our convenience, we have included two columns **Normal Text** and **Text without Unwanted Space**. Here, we have unwanted spaces between the word **Abagail** and the quotation mark** (“)**.

## Download Practice Workbook

## 6 Quick Ways to Remove Space in Excel after Text

Now we will look for ways to remove space in Excel after text. In this case, we will remove the spaces between **Abagail **and the **quotation mark (“ ”)**. The following methods will guide us through different methods to do so.

### 1. Remove Spaces in Excel after Text Using Find and Replace

We can remove spaces after text using **Find and Replace** feature of Excel. To do so, first, we have to select the range over which we want to apply the **FInd and Replace** feature.

Here, I selected the range **B5:B9**.

First, we will go to the** Home** tab then we will go to **Find & Select **option in the toolbar.

From **Find & Select** select **Replace**.

** **

Then, the **Find and Replace** dialogue box will appear.

Then in **Find what** box we would type a **SPACEBAR** once. In this case, we will leave the **Replace with** box **blank**.

Then we would press **Replace All **to **remove all the spaces**. A **message box** will appear with the number of replacements.

All the spaces after text will disappear.

**Read More: How to Remove Space Before Text in Excel (4 Methods)**

### 2. Remove Spaces after Text Using the TRIM Function in Excel

#### 2.1. Using TRIM Function Only

**TRIM Function** in Excel helps us remove any unwanted space. We can use **TRIM Function** to get rid of any unwanted spaces after the text. Here, in this case in cell **C5**, we would type

`=TRIM(B5)`

After pressing the **ENTER **key, we would find the **text without any unwanted space**. Here we would get the unnecessary spaces between two parts of the name removed.

Now, we would use the **Fill Handle** to use the **AutoFill** feature of Excel to remove space from other values in the **Text Without Unwanted Space column**.

#### 2.2. Using LEN and LEFT Function with TRIM Function

**LEN Function** in Excel measures the length of any number or string. **LEFT Function** in Excel can be useful to retrieve a specified number of characters from any number or text string. Here, we will use a combination of three functions to remove spaces after text. Here, in cell **C5**, we type

`=TRIM(LEFT(B5,LEN(B5)))`

**Formula breakdown **

** LEN(B5)**—> measures the length of the characters in cell B5. In this case,

**Output is**: 14

**LEFT(B5,LEN(B5))**—> becomes LEFT(B5,14). So,it takes into account 14 characters from the left. In this case,** Output is** : Abagail Ross** Explanation :** Notice that, there are 2 extra spaces between the words.

**TRIM(LEFT(B5,LEN(B5)))**—> now the TRIM Function comes into action. It** removes the two extra spaces** between the words and gives us the appropriate result.** Output is** : Abagail Ross**Explanation :** Notice that there is no extra space between two words.

Pressing the **ENTER** key, we get the result in **C5 **cell.

Using the **Fill Handle** to use the **AutoFill** feature of Excel, we get values in the **Text Without Unwanted Space column**.

**Read More: How to Remove Leading Spaces in Excel (4 Methods)**

### 3. Remove Spaces after Text Using Power Query

**Power Query** is a useful feature when it comes to data formatting. We can use **Power Query**to clean unwanted spaces. First, we select all the data of the range we are working with.

We selected the range **B5:B9.**

Then, to perform **Power Query**, we need to select **From Table/ Range** from the **Data tab**.

A new **window** will pop up.

Go to **Add Column**>> **Format**>> **TRIM**

** **

** **

We will get the trimmed data in a new **column** named **Trim**.

Then go to the **File** tab.

Click **Close & Load**.

You will get the results in a **new sheet**.

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

### 4. Remove Spaces after Text Using Macros and VBA

We can use **VBA** to remove spaces after the text. First, we need to select the entire range of which we want to remove spaces.

Then, we selected the range **B5:B9**.

To use the **VBA **editor,

Open the **Developer **tab >> select **Visual Basic**

A new window will appear.

Go to **Insert** tab >> select **Module**.

A new **Module** will appear. Type the code below in the new **Module**.

```
Sub RemoveSpacesafterText()
Dim M As range
Dim cell As range
Set M = Selection
For Each cell In M
If Not IsEmpty(cell) Then
cell = Trim(cell)
End If
Next cell
End Sub
```

We will create a new **Sub Procedure** *RemoveSpacesafterText* and declare two variables **M** and **cell** as **Range**.

Then we used **For loop** to find every cell that has unwanted spaces. We will use the **VBA** **TRIM** function to remove spaces.

Now, we will **save** the code and **run **the code by pressing **F5**.

We will be able to remove all the unwanted spaces.

**Read More:** **How to Remove Trailing Spaces in Excel (6 Easy Methods)**

### 5. Remove Spaces after Text Using LEFT Function in Excel

**LEFT **function in Excel lets us choose a specified number of characters from the left of the text. If we know how many spaces there are in the right of the text i.e., after the text we can remove the spaces using the **LEFT **function.

In this case, we know that we have **three spaces** on the right of the text. So in **cell C5,** we will type

`=LEFT(B5,LEN(B5)-3)`

**Formula Breakdown**

** LEN(B5)** —> gives us the length of the value in cell B5.

**Output is**: 10

** LEN(B5)-3** —> gives us the value of 10-3.

**Output is**: 7

**Explanation**: We find the number of characters required.

** LEFT(B5,LEN(B5)-3)** —> retrieves 7 characters from the left of the text.

**Output is**: Abagail

**Explanation**: No extra space after the text.

Pressing the **ENTER** key we will get the value in **cell C5**.

Now by dragging down the **Fill Handle **to use the **AutoFill **feature we will get all the values in the **Text Without Unwanted Space column**.

### 6. Remove Spaces after Text Using SUBSTITUTE Function in Excel

**SUBSTITUTE** **Function** of Excel lets us replace one text with another. We can use the **SUBSTITUTE Function** to replace the unwanted spaces. Here, we will replace the extra spaces between two texts. To do so, in the **C5 cell **we will type

`=SUBSTITUTE(B5," ","")`

In the formula, we will select the **B5 cell** the value of which we want to replace. Then used **space **as **old_text** as I want to remove space. Then used **blank space** within the double quotation mark as **new_text**.

Now, the **SUBSTITUTE** function will substitute the extra spaces of after text with no space.

After pressing **ENTER **key we will get the desired value in the **B5 cell**.

Then using the **Fill Handle** to use the **AutoFill** feature we will get the corresponding values in the **Text Without Unwanted Space column**.

**Read More: How to Remove Space in Excel before Numbers (3 Ways)**

## Things To Remember

Unwanted spaces can cause us problems when we are using **LOOKUP Functions**.

When we are using **LEFT** **Function **only for removing unwanted spaces after text we should know the number of spaces after the text.

## Practice Section

We have included a practice section so that you can practice the methods on your own.

## Conclusion

We tried to show 6 quick ways to remove space in Excel after text. Hope these will be useful to you in your daily activities. If you need any further clarifications, feel free to comment below or send any feedback. Our team will be happy to help you out with any of your problems.

Thank you. This helped me to remove spaces in the end of text through Power query.