7 Ways to Remove Blank Spaces in Excel

As an Excel user, you have likely encountered the problem of having unwanted spaces within your cell contents.

Whether it’s due to importing data from another source, copy-pasting, or simply typing too quickly, it’s important to know how to swiftly remove those blank spaces to avoid errors in your work.

In this article, we will provide you with 7 methods to quickly remove spaces within Excel cell contents.

Method 1: Using the TRIM Function to Remove Blank Spaces in Excel

The TRIM function is a quick method to remove spaces from the beginning and end of a cell’s contents. It can be used to remove all extra spaces in a cell, leaving only a single space between words as shown in the example below:

 

This function is particularly useful for correcting text data that contains irregular spacing, such as leading, and trailing.

Method 2: Using Find and Replace

Find and Replace is another great tool to remove spaces within cells. Follow the instructions below:

• Press Ctrl + F or go to the “Home” tab on the ribbon and select “Find & Replace”.
• In the “Find what” field, enter a single space ” “.
• Leave the “Replace with” field blank.
• Click “Replace All.”

Method 3: Using SUBSTITUTE Function to Remove blank Spaces in Excel

You may also use SUBSTITUTE Function to remove spaces cell-by-cell. Here’s an example that can be copied down:

=SUBSTITUTE(A1,” “,””)

The SUBSTITUTE function in Excel is a versatile and powerful tool designed to replace specific text in a cell with new text.

It works by searching for a specified substring within a given cell and then replacing all occurrences of this substring with a new string. The SUBSTITUTE function is ideal for tasks such as removing unwanted characters, correcting misspellings, or standardizing text formats.

Method 4: Using Excel’s TEXT to Columns feature

The “Text to Columns” feature in Excel is a powerful tool that can be used to split the contents of a cell into separate columns. Follow the instructions below:

• Select the range of cells that contains the data you want to split.
• Go to the “Data” tab on the ribbon, then click “Text to Columns”.
• Select “Delimited” and click “Next”.
• Uncheck all delimiter options such as tabs and commas, then check the “Space” option.
• Click “Finish.”

Method 5: Using VBA to remove spaces

For advanced users, a VBA macro can be created to remove spaces within Excel cell contents programmatically. Here’s an example code that can be run in a new module:

Sub RemoveSpaces()
Dim cell As Range
For Each cell In Selection
If InStr(cell.Value, " ") > 0 Then
cell.Value = Replace(cell.Value, " ", "")
End If
Next cell
End Sub

 

Method 6: Using Flash Fill to remove spaces

Flash Fill is a tool in Excel that automatically fills in values based on patterns it recognizes. This is an alternative to using Find and Replace or functions. Here’s how you can use it:

• Type the corrected text in a new column next to the original text. For example, if cell A2 has “Ea st” and you want to remove the spaces, type “East” next to it in cell B2.
• Excel should recognize the pattern after you type the first few cells. Press Ctrl + E to fill in the rest of the column.

Method 7: Using Power Query to remove spaces

Power Query is an add-in for Excel that allows you to import, transform, and clean data easily. It has a variety of options for removing spaces within Excel cell contents. Here’s how you can use it:

• Select the range of cells containing the data you want to transform.
• Go to the “Data” tab on the ribbon, then click “From Table/Range” to open the Power Query editor.
• Select the columns you want to transform by clicking on their column headers.
• Go to the “Transform” tab on the ribbon, then click “Replace Values”.
• In the “Replace Values” dialog box, in the “Value To Find” field, enter a single space ” “.
• Leave the “Replace With” field blank, then click “OK”.

 

Conclusion

In conclusion, there are several methods available to remove spaces within Excel cell contents. Whether you need to remove spaces cell-by-cell or on a larger scale, Excel offers multiple options to get the job done quickly and efficiently. Try out these methods for yourself to find the best one that works for you.