Mrinmoy Roy
Having spaces in texts is quite a common thing. But having unnecessary spaces is a real issue that needs to be resolved. In this article, you will learn six different ways that you can use to remove spaces in a cell in Excel with ease. Now, let’s start this article and explore these methods.
The following image demonstrates an overview of the six methods to remove spaces in Excel that we will learn in this article.
Download Practice Workbook
6 Efficient Methods to Remove Spaces in Cell in Excel
In this section of the article, we will discuss six efficient methods to remove spaces in a cell in Excel. Here, we will be using the Best Movie List as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:
So, without having any further discussion let’s dive straight into all the methods one by one.
1. Using TRIM Function to Remove Spaces in Cell
The easiest way to remove spaces in a cell in Excel is to use the TRIM function. Now, follow the steps below to see how to use this function.
Steps:
- First of all, select cell D5 to store the trimmed texts.
- After that type the following formula in cell D5.
=TRIM(B5)- Then press the ENTER button.
- Now, drag the Fill Handle icon to the end of the Trimmed column and the spaces will be eliminated as shown in the image below.
Note: This is the best as well as easiest way to remove spaces from the text in Excel. You are recommended to use this method prior to any other methods.
Read More: How to Remove Space in Excel after Text (6 Quick ways)
2. Applying SUBSTITUTE Function
You can use the SUBSTITUTE function to substitute all the spaces in your texts with null strings. So, you can use this function in lieu of the TRIM function. But the problem is this function removes all the spaces including the space in between words. Which is not desired in most cases. So, be careful while using this method. You can use this method only when you can ignore the spaces in between words. Anyways, here are the steps to follow to see how to use this function.
Steps:
- First of all, select cell D5 and type the following formula.
=SUBSTITUTE(B5," ","")- Then, press the ENTER button.
- Now drag the Fill Handle icon to the end of the Trimmed column.
Read More: Remove All Spaces in Excel
3. Utilizing CLEAN with TRIM Function to Eliminate Spaces in Cell
In this method, we will combine the CLEAN function as well as the TRIM function to remove spaces in a cell in Excel. Here, the CLEAN function can remove all the non-printable characters. So the CLEAN function along with the TRIM function removes all the non-printable characters as well as spaces from texts in Excel. Now, go through the steps below to see how can we combine them to remove spaces in a cell in Excel.
Steps:
- Firstly, select cell D5 and insert the formula given below.
=TRIM(CLEAN(B5))Formula Breakdown
- Here, the CLEAN function has only one argument.
- In this case, B5 is the text argument.
- Output → ” Pulp Fiction “.
- Now, TRIM(CLEAN(B5)) becomes → TRIM(” Pulp Fiction “).
- Here, ” Pulp Fiction “ is the text argument in the TRIM function.
- Output → Pulp Fiction.
- Then press the ENTER button.
- Now drag the Fill Handle icon to the end of the Trimmed column.
Note: You can use this method in exchange for using the TRIM function. But what’s good about this method is that using this method you can remove spaces as well as all the non-printable characters which the TRIM function alone can’t accomplish.
Similar Readings
- How to Remove Space Before Text in Excel
- Remove the Trailing Spaces in Excel (2 Easy Ways)
- How to Remove Leading Spaces in Excel (4 Methods)
- How to Remove Space after Number in Excel (6 Easy Ways)
4. Implementing TRIM, CLEAN, and SUBSTITUTE Functions
In this section of the article, we will use a combination of the TRIM, CLEAN, and SUBSTITUTE functions to remove spaces in a cell in Excel. Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, insert the following formula in cell D5.
=TRIM(CLEAN((SUBSTITUTE(B5," "," "))))Here, cell B5 represents the first cell of the Movie column.
Formula Breakdown
- Firstly, in the SUBSTITUTE function,
- B5 → This is the text argument.
- ” “ → It represents the old_text argument.
- ” “ → It refers to the new_text argument.
- Output → ” Pulp Fiction “.
- Now, the CLEAN function returns ” Pulp Fiction “ as output.
- Finally, TRIM(CLEAN((SUBSTITUTE(B5,” “,” “)))) becomes → TRIM(” Pulp Fiction “).
- Output → Pulp Fiction.
- Following that, press ENTER.
- Lastly, drag the Fill Handle to get the rest of the outputs as demonstrated in the following image.
Read More: How to Remove Spaces in Excel with Formula (5 Quick Ways)
5. Removing Spaces in Cell Using Find and Replace Feature
We can remove all the extra spaces from our text in Excel using the Find and Replace feature. The Find and Replace feature is a built-in option of Microsoft Excel. To use this feature, all you need to do is to follow the steps explained below.
Steps:
- Firstly, select the cells from which you want to remove the spaces.
- After that, use the keyboard shortcut CTRL + H to open the Find and Replace dialogue box.
- Now, type in one space inside the Find what field in the Find and Replace dialogue box.
- Then, keep the Replace with field empty.
- Subsequently, click on the Replace All option.
- Following that, a confirmation dialogue box will appear and click OK there.
- Finally, click on Close in the Find and Replace dialogue box.
Consequently, the spaces will be removed from the selected cells as shown in the following picture.
Read More: How to Remove White Space in Excel (6 Easy Ways)
6. Incorporating VBA Code to Remove Spaces
In the final method of the article, we will incorporate the VBA Macro feature of Excel to remove spaces in a cell. So, let’s use the instructions outlined below to do this.
Steps:
- Firstly, go to the Developer tab from Ribbon.
- Following that, click on the Visual Basic option from the Code group.
As a result, the Microsoft Visual Basic for Applications window will open on your worksheet.
- Now, go to the Insert tab in the Microsoft Visual Basic for Applications window.
- Then, choose the Module option from the drop-down.
- After that, write the following code in the newly created Module.
Function removing_space(input_string) As String
Dim selected_string As String
Dim new_string As String
Dim temp As String
Dim a As Integer
selected_string = input_string
For a = 1 To VBA.Len(selected_string)
temp = Left(Mid(selected_string, a), 1)
If temp = " " Or temp = " " Then
Else
new_string = new_string & "" & temp
End If
Next a
removing_space = new_string
End Function
Code Breakdown
- Firstly, we created a function named removing_space and specified its arguments along with its data type.
- After that, we declared three variables named selected_string, new_string, and temp as String.
- Then, we introduced another variable named a as Integer.
- Following that, we assigned the value of the input_string into the selected_string
- Afterward, we use a For Next loop with an IF statement to remove the space from the selected_string
- After that, we ended the IF statement and closed the For Next
- Next, we assigned the current value of the new_string variable to the removing_space.
- Finally, we terminated the function.
- After writing the code, click on the Save option.
- Afterward, use the keyboard shortcut ALT + F11 to return to the worksheet.
- Then, apply the following formula in cell D5.
=removing_space(B5)Here, cell B5 indicates the first cell of the Movie column.
- Next, press ENTER and the spaces will be removed from cell B5.
- Finally, use Excel’s AutoFill option to get the rest of the outputs as demonstrated in the following image.
Read More: How to Remove Spaces in Excel: With Formula, VBA & Power Query
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Conclusion
To sum up, we have discussed 5 ways, to remove spaces in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.
