Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Remove Spaces in Cell in Excel (6 Easy Methods)

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.

Overview of the six methods to remove spaces in cell in Excel


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:

Sample 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.

Using the TRIM function to remove spaces in cell in Excel

  • 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.

Outputs got after using the TRIM function

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 Extra Spaces in Excel


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.

Using the SUBSTITUTE function to remove spaces in cell in Excel

  • Now drag the Fill Handle icon to the end of the Trimmed column.

Outputs obtained by applying the SUBSTITUTE function

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.
    • OutputPulp Fiction.
  • Then press the ENTER button.

Using a combination of the CLEAN, and the TRIM functions

  • Now drag the Fill Handle icon to the end of the Trimmed column.

Output got after removing the spaces

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:


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  “).
    • OutputPulp Fiction.
  • Following that, press ENTER.

Implementing a combination of TRIM, CLEAN, and SUBSTITUTE functions to remove spaces

  • Lastly, drag the Fill Handle to get the rest of the outputs as demonstrated in the following image.

Outputs achieved after removing the spaces


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.

Utilizing the keyboard shortcut to access the Find and Replace feature

  • 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.

Specifying required parameters in the Find and Replace dialogue box to remove spaces in cells

  • Following that, a confirmation dialogue box will appear and click OK there.

Confirmation dialogue box after the spaces are removed

  • Finally, click on Close in the Find and Replace dialogue box.

Closing the Find and Replace dialogue box

Consequently, the spaces will be removed from the selected cells as shown in the following picture.

Outputs obtained after removing spaces using the Find and Replace option in Excel


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.

Using the Developer option to access the VBA editor

As a result, the Microsoft Visual Basic for Applications window will open on your worksheet.

Microsoft Visual Basic for Applications window

  • Now, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Then, choose the Module option from the drop-down.

Inserting new Module to write VBA code

  • 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

Writing VBA code

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.

Saving written VBA code

  • 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.

Using newly created custom function to remove spaces in cell in Excel

  • Finally, use Excel’s AutoFill option to get the rest of the outputs as demonstrated in the following image.

Outputs got by using the new custom function


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Sample Practice Section provided in each worksheet of the Practice Workbook


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.


Read More

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo