How to Remove Space in Excel after Text (6 Quick ways)

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 (“).

How to Remove Space in Excel after Text: 6 Quick Ways

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

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

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

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

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.

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.

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

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

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

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.

Download Practice Workbook

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.

Related Articles

• How to Remove Tab Space from Excel
• Remove Space between Rows in Excel
• How to Remove the Trailing Spaces in Excel
• How to Remove White Space in Excel
• How to Remove Blank Spaces in Excel
• How to Find and Replace Space in Excel
• How to Remove Space after Number in Excel

<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel