How to Remove Spaces in Excel: With Formula, VBA & Power Query

In Microsoft Excel, there are several effective methods available to remove all sorts of unwanted spaces. You’ll get to know all possible and convenient methods in this article to erase leading, trailing, blank, white and non-breaking spaces with simple examples and illustrations.

Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article.

11 Suitable Ways to Remove Spaces in Excel

1. Use Excel TRIM Function to Remove All Leading & Trailing Spaces

The TRIM function removes all spaces from a text string except for single spaces between words. In the following table, there are some texts lying in the Text column. The texts are not in proper format since all of them have extra blank spaces in between.

Now we’ll use the TRIM function for all texts to remove these extra and unnecessary spaces.

The required formula in the output Cell C5 will be:

After pressing Enter and auto-filling the rest of the cells in the Output column, we’ll get all texts in the same and exact format.

2. Apply Find & Replace Tool to Erase Extra Spaces in Excel

We can use Find & Replace tool from the Home tab to delete extra spaces. But with this method, we can remove only those spaces which are identical in the count. If the texts contain a random or different number of spaces among them then this method is not suitable to remove white spaces or all blanks. In that case, we have to apply this feature again and again until the last replacement is made.

Let’s apply this feature now to the texts with different numbers of spaces among them.

📌 Step 1:

➤ Press CTRL+H to open the Find and Replace tool.

➤ In the Find what box, press the Space button twice.

📌 Step 2:

➤ Tap Space only once in the Replace with box.

➤ Press the Replace All button.

A message box will pop up displaying the total number of replacements made in the worksheet. But if we now notice the data table, we’ll see that the texts have still different numbers of blank spaces among them. To remove these spaces, we have to repeat the procedure at a stretch.

📌 Step 3:

➤ Keep the Find and Replace window open since the previous replacements were made.

➤ Press Replace All button once again.

Now we’ll find that more spaces have been removed from the data table. And the message box tells us now that 33 replacements have been made which was 67 in the first application. So, it means the number of unnecessary spaces is now decreasing gradually with the repetitions of the same procedure.

Let’s click on the Replace All button again and it’s now showing 12 more replacements have just been made.

Repeat the procedure and we’ll find that the lengths of our texts are decreasing which indicates that the texts are coming to a normal format with repeated steps. But don’t stop pressing the Replace All button right now.

And now probably we’ve reached our last occurrence of pressing the Replace All tab. If you look carefully at the text data, there are only leading spaces remaining. But unfortunately, we cannot remove these leading spaces with this Find and Replace tool. Rather, we have to apply the TRIM function here to remove all leading spaces here.

To check if we can replace more double spaces with single spaces, we can press Replace All button once again and now a different message box will appear saying that the tool can’t find anything to replace. So, when this window will appear, we can be certain of our operations to stop.

3. Delete Unnecessary Spaces along with Line Breaks

When we copy a text from another source, sometimes the text might appear with line breaks too. The line break is a point where a text is split into two lines. The following are some of the examples of the texts with line breaks. They have even extra spaces too.

To remove line breaks, only the TRIM function is not sufficient. We have to combine it with the CLEAN function too. The CLEAN function removes all non-printable characters from the text.

So, the required formula in Cell C5 should be:

And you’ll find the text with no more line breaks or any unwanted spaces.

4. Remove Non-Breaking Spaces with Combined Formula

After erasing all unnecessary spaces, non-printable characters and line breaks there can still be some stubborn spaces lying in the text. We call them non-breaking spaces and they look like regular spaces. So sometimes it might be difficult to find those unwanted spaces. Moreover, the combination of TRIM and CLEAN functions is not sufficient to erase these non-breaking spaces as shown in the picture below.

Non-breaking spaces usually appear with the CHAR function having an ASCII code of 160. To remove these stubborn spaces, we have to use the SUBSTITUTE function to replace the ASCII code – 160 with a regular space. And if we combine TRIM, CLEAN, and SUBSTITUTE functions together, we can anticipate erasing all possible blank spaces from the text.

The required and combined formula in Cell C5 will be then:

And thus we can get back our texts to a regular format finally.

Read More: Remove Extra Spaces in Excel

5. Insert SUBSTITUTE Function to Erase All Spaces in Excel

The SUBSTITUTE function in Excel replaces existing text with a new text in a text string. By using this function, we can remove all spaces present in a cell. The required formula with the SUBSTITUTE function to replace a regular space with no space will be:

After pressing Enter, we’ll find our texts with no single space anymore.

Read More: Remove All Spaces in Excel

6. Use of Excel MID Function to Delete Leading Spaces Only

Assuming that we want to remove only leading spaces from the text. A combined formula with the uses of MID, FIND, TRIM, and LEN functions can serve the purpose with ease.

The MID function returns the characters from the middle of a text string, given a starting position and text length. The FIND function returns the starting position of one text string within another text string and this function is case-sensitive. And the LEN function counts the length of all characters in a text string.

To delete the leading spaces only, the required combined formula in the output Cell C5 will be:

Now press Enter and we’ll find our text with no leading spaces at all.

🔎 How Does the Formula Work?

• LEN(B5): The LEN function here counts the characters of the text in Cell B5 and returns 20.
• TRIM(B5): The TRIM function removes all regular spaces from B5 and returns “Adam Milne”.
• MID(TRIM(B5),1,1): The MID function in this section returns the starting character of the text which is “A”.
• FIND(MID(TRIM(B5),1,1),B5): This part of the formula returns the starting character of “A” from Cell B5 and that is 11.
• Finally, the entire formula starts the text based on the starting character (11) found in the previous section.

Read More: Remove Leading Spaces in Excel

7. Embed VBA Codes to Remove Only Leading Spaces in Excel

If you opt to choose coding with VBA script then this section should be applicable for you. We’re going to remove all leading spaces from a selected range of cells here. Let’s go through the following steps to find how this code works out.

📌 Step 1:

➤ Press ALT+F11 to open the VBA window first.

➤ From the Insert tab, select the Module option.

📌 Step 2:

➤ In the module window, copy and paste the following codes:

Option Explicit

Sub DeleteLeadingSpaces()
Dim Rng1 As Range
Dim Rng2 As Range
Dim TitleName As String
On Error Resume Next
TitleName = "Remove Leading Spaces"
Set Rng2 = Application.Selection
Set Rng2 = Application.InputBox("Input Cell Range", TitleName, Rng2.Address, Type:=8)
For Each Rng1 In Rng2
    Rng1.Value = VBA.LTrim(Rng1.Value)
Next
End Sub

➤ Press F5 to run the VBA macro.

📌 Step 3:

➤ As shown in the following picture, the Macros window will appear to save and run this macro. Press Run only.

📌 Step 4:

➤ In the application box, input the range of cells containing texts from where you want to erase the leading spaces.

➤ Press OK and you’re done.

All the texts in the selected range of cells will have no leading spaces now.

8. Excel VBA to Remove Only Trailing Spaces

Trailing spaces are hard and irritating to find since they are not visible. The user has to enable editing a cell to check if it incorporates any trailing spaces. But we can go for the VBA codes once again to remove all trailing spaces from the selected range of cells at once.

The required codes to remove all trailing spaces from the texts are given below:

Option Explicit

Sub EraseTrailingSpaces()
Dim Rng1 As Range
Dim Rng2 As Range
Dim TitleName As String
On Error Resume Next
TitleName = "Remove Trailing Spaces"
Set Rng2 = Application.Selection
Set Rng2 = Application.InputBox("Input Cell Range", TitleName, Rng2.Address, Type:=8)
For Each Rng1 In Rng2
    Rng1.Value = VBA.RTrim(Rng1.Value)
Next
End Sub

While saving and playing the macro, you’ll see the application box to input the range of cells from where all trailing spaces have to be deleted.

After you’ll execute the application, you can verify the text once again in a cell if it still has a trailing space. And now you shouldn’t expect to find any trailing space here while checking.

Read More: Remove the Trailing Spaces in Excel

9. Remove Particular Spaces from Text in Excel

Sometimes we may need to remove spaces from a specific occurrence or instance in a text. In the following dataset, there are unwanted spaces before commas (,). And this space character is the second occurrence in each text.

The SUBSTITUTE function allows us to replace this space with no space based on its instance number in the text. The required formula in the output Cell C5 to erase this unwanted space will be:

After applying the formula, the texts will look like the following. They have no unwanted spaces before commas anymore.

Read More: Remove Space in Excel after Text

10. Delete Spaces from Entire Excel Sheet or Workbook

By using the Find & Replace tool, we can remove all spaces from the current worksheet or even from an entire workbook in seconds. What we have to do is simply put a space in the Find what box first.

And then we have to select the option- Sheet or Workbook from where we want to remove all spaces. After pressing Replace All button, you’ll find no space anymore in the selected region (Sheet or Workbook).

11. Using Excel Power Query to Erase Blank Spaces

In our last section, we’ll use the Power Query Editor to trim and remove unnecessary spaces from the selected texts. The Email Address column in the following screenshot has some email addresses with several unwanted spaces in different positions. Since these addresses are not supposed to contain any space inside, we have to remove all spaces here. So, let’s have a look at the following steps to see how we can erase these unwanted spaces with Power Query Editor.

📌 Step 1:

➤ Go to the Data tab and select the From Table/Range command first. A window named Create Table will open up.

📌 Step 2:

➤ Select the entire data table along with the headers.

➤ Put a check mark on the option- ‘My table has headers’.

➤ Press OK and your data table will now be visible in the Power Query window.

Here’s a visual representation of the Power Query Editor with our data table.

📌 Step 3:

➤ Select the Email Address column and open the Context Menu by right-clicking the mouse.

➤ From the Transform sidebar menu, select Trim.

And you’ll find that only leading spaces have been removed. The Trim command in the Power Query is not exactly similar to the TRIM function available in the Excel spreadsheets. The Trim command in the Power Query only removes the leading and trailing spaces but it is unable to erase any space inside the text. So we have to use the Replace command now to remove all spaces from the texts.

📌 Step 4:

➤ Now launch the Context Menu once again.

➤ And select the Replace values option.

📌 Step 5:

➤ In the Value to Find box, put a space once.

➤ Keep the Replace With box empty.

➤ Press OK.

Now the email addresses have no more unwanted or blank spaces inside. And when we have to remove all spaces from any position in a text, it’s better to use the Replace command in the Power Query. The Trim command is suitable to remove only leading and trailing edges when needed.

📌 Step 6:

➤ Since we have to load the data from Power Query Editor to our Excel spreadsheet, so now press the Close & Load button situated at the top-left corner of the window.

The Power Query window will shut down and it will open a new worksheet in Excel to transfer the entire and modified data in a filtered table. Now we can copy this data from the table to our desired region in any other worksheet.

Read More: Remove Blank Spaces in Excel

Concluding Words

I hope all of the methods mentioned in this article will now help you to apply them in your Excel spreadsheets when you need to erase any sort of unwanted spaces from the texts. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Remove Space in Excel: Knowledge Hub

• Find and Replace Space in Excel
• Remove Space in Excel Before Numbers
• Remove Space after Number in Excel
• Remove Space Before Text in Excel
• Remove Space between Rows in Excel
• Remove Tab Space from Excel

<< Go Back To Data Cleaning in Excel | Learn Excel