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.
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
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.
3. Remove Spaces after Text Using Power Query
Power Query is a useful feature when it comes to data formatting. We can use Power Queryto 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.
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
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
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.
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.
We have included a practice section so that you can practice the methods on your own.
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.