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.
=TRIM(B5)
- 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.
=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 : 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.
- 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:
=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.
- 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:
=SUBSTITUTE(B5," ","")
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
Get FREE Advanced Excel Exercises with Solutions!
Thank you. This helped me to remove spaces in the end of text through Power query.