How to Remove Spaces in a Cell in Excel (5 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 5 different ways that you can use to remove spaces in a cell in Excel with ease.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


5 Methods to Remove Spaces in a Cell in Excel

In this article, 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:

dataset for 5 Ways to Remove Spaces in a Cell in Excel

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Use TRIM Function to Remove Spaces in a Cell in Excel

The easiest way to remove spaces in a cell in Excel is to use the TRIM function.

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

=TRIM(B5)
within the cell.

❸ Then press the ENTER button.

Use TRIM Function to Remove Spaces in a Cell in Excel

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

fill handle

That’s it.

💡 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. Use SUBSTITUTE Function to Remove Spaces in a Cell in Excel

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 ▶ to store the trimmed texts.

❷ After that type

=SUBSTITUTE(B5," ","")
within the cell.

❸ Then press the ENTER button.

Use SUBSTITUTE Function to Remove Spaces in a Cell in Excel

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

Fill Handle icon to the end of the Trimmed column

That’s it.

Read more: Remove All Spaces in Excel


3. Use CLEAN with TRIM Function to Delete Any Kinds of Spaces in a Cell in Excel

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 collab them to remove spaces in a cell in Excel.

🔗 Steps:

❶ First of all, select cell D5 ▶ to store the trimmed texts.

❷ After that type

=TRIM(CLEAN(B5))
within the cell.

❸ Then press the ENTER button.

Use CLEAN with TRIM Function to Delete Any Kinds of Spaces in a Cell in Excel

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

That’s it.

💡 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. Use Find and Replace to Delete Extra Spaces in a Cell in Excel

We can remove all the extra spaces from our text in Excel using the Find and Replace feature. But as we have to search for the spaces first, we need to know how many spaces are present within our text. As soon as we know the number of extra spaces, we can easily replace them with a single space using the find and replace feature which is a built-in option of Microsoft Excel. To use this feature, all you need to do is,

❶ Press CTRL + H to open up the Find & Replace dialog box.

❷ Press the Space key 3 times within the Find what bar and once within the Replace with bar.

As we have 3 extra spaces in our sample dataset.

❸ After that click on the Replace All option.

Use Find and Replace to Delete Extra Spaces in a Cell in Excel

When you are done with all the steps above, you will get the texts without unnecessary spaces within them as follows:


5. Use VBA Code to Remove Extra Spaces in a Cell in Excel

Excel enables us to create user-defined functions that we can use to remove extra spaces in a cell in Excel. Now use the following code to remove spaces in a cell by creating a user-defined function.

🔗 Steps:

❶ Press ALT + F11 to open up the VBA code editor.

❷ Now go to Insert ▶ Module.

❸ After that copy the following code:

Sub RemoveSpacesIny()

Dim x As Range
Dim y As Range
Dim z As String

Set x = Selection

For Each y In x
    z = y.Value
    z = Trim(z)
    y.Value = z
Next y

End Sub

❹ Then paste and save the code in the VBA code editor.

❺ After that return back to the Excel worksheet and select the range of cells from where to remove the extra spaces.

❻ Now press ALT + F8 to open up the Macro window.

❼ Select the function RemoveSpaceInCell that you’ve created beforehand.

❽ Finally, click on Run to run the function.

alt + f8 macro window open

After completing all of the things, your data table will look like this:


Things to Remember

📌 Press ALT + F11 to open the VBA editor.

📌 To open up the Macro dialog box press ALT + F8.


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