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

Remove Space after Text in Excel

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

Remove Space in Excel after Text


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.

Remove Space in Excel after Text

From Find & Select select Replace.

Then, the Find and Replace dialogue box will appear.

Remove Space in Excel after Text

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

Remove Space in Excel after Text

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)

Remove Space in Excel after Text

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.

Remove Space in Excel after Text


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.

Remove Space in Excel after Text

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 Queryto clean unwanted spaces. First, we select all the data of the range we are working with.

Remove Space in Excel after Text

We selected the range B5:B9.

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

Remove Space in Excel after Text

A new window will pop up.

Go to Add Column>> Format>> TRIM

 

Remove Space in Excel after Text

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

Remove Space in Excel after Text

Then go to the File tab.

Click Close & Load.

Remove Space in Excel after Text

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.

Remove Space in Excel after Text

To use the VBA editor,

Open the Developer tab >> select Visual Basic

A new window will appear.

Remove Space in Excel after Text

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

Remove Space in Excel after Text

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.

Remove Space in Excel after TextRead 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.

Remove Space in Excel after Text

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.

Remove Space in Excel after Text


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.

Remove Space in Excel after Text

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.

Remove Space in Excel after Text


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

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

Leave a reply

ExcelDemy
Logo