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

We have a list of names with unwanted spaces after text. We want to remove those spaces to perform further operations. We have included two columns, Normal Text and Text without Unwanted Space.

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

Method 1 – Remove Spaces in Excel after Text Using Find and Replace

• Select the range to apply the Find and Replace feature. We selected the range B5:B9.

• Go to the Home tab and click on the Find & Select option in the toolbar.

• From Find & Select, select Replace.

• The Find and Replace dialogue box will appear.

• In the Find what box, put a single space.
• Leave the Replace with box blank.

• Press Replace All. 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

Method 2 – Remove Spaces after Text Using the TRIM Function in Excel

Case 2.1 – Using the TRIM Function Only

• Use the following formula in cell C5.

• Press Enter.

• Drag the Fill Handle down to AutoFill the column.

Case 2.2 – Using LEN and LEFT Functions with the TRIM Function

• Use the following function in C5.

• Press Enter.

• Drag the Fill Handle down to AutoFill the column.

Read More: How to Remove Leading Spaces in Excel

Method 3 – Remove Spaces after Text Using Power Query

• Select the entire dataset.

• We selected the range B5:B9.

• Select From Table/ Range from the Data tab.

• A new window will pop up.

• Go to Add Column, then to Format, and select TRIM.

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

• Go to the File tab.

• Click Close & Load.

• You will get the results in a new sheet.

Method 4 – Remove Spaces after Text Using Macros and VBA

• Select the entire range.

• We selected the range B5:B9.

• Open the Developer tab and select Visual Basic

• A new window will appear.

• Go to the Insert tab and select Module.

• A new Module will appear. Insert 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 used a For loop to find every cell that has unwanted spaces. The VBA TRIM function will remove spaces.

• Save the code and run it by pressing F5.

Method 5 – Remove Spaces after Text Using the LEFT Function in Excel

If we know how many spaces there are on the right of the text (i.e., after the text), we can remove the spaces using the LEFT function.

• We have three spaces on the right of the text. In cell C5, insert the following:

• Press Enter key to get the value in cell C5.

• By dragging down the Fill Handle to use the AutoFill feature, we will get all the values in the Text Without Unwanted Space column.

• However, if you have a different number of spaces in other cells, the formula won’t work.

Method 6 – Remove Spaces after Text Using the SUBSTITUTE Function in Excel

We will replace the extra spaces between two words.

• In the C5 cell, use the following formula:

The SUBSTITUTE function will substitute any instance of two spaces it can find with a blank character.

• Press Enter.

• Use the Fill Handle to AutoFill the corresponding values in the Text Without Unwanted Space column.

Read More: How to Remove Space in Excel before Numbers

Practice Section

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

Download the Practice Workbook

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