How to Remove Blank Spaces in Excel (7 Ways)

If your data set contains blank spaces which are unnecessary you may need to remove them. In this article, I’ll show you seven effective ways to remove blank spaces in Excel.

Let’s say, We have a dataset where different cells have multiple blank spaces. Now we will remove these blank spaces.

How to Remove Blank Spaces in Excel: 7 Quick Ways

1. TRIM Function to Remove Blank Spaces

You can easily remove blank spaces by

using the TRIM function. Type the following formula in an empty cell (A16),

=TRIM(A5)

Here, the TRIM function will remove extra blank spaces from the selected cell A5.

Press ENTER and You’ll find the text without blank spaces in cell A16.

Drag cell A16 to apply the same formula for all other cells in column A.

2. Find and Replace Command

Using Find and Replace command is another way to remove blank spaces. First, select the cells from where you want to remove blank spaces and go to Home> Editing > Find & Select > Replace

Now, a Find and Replace window will appear. Insert single space in Find what box and click on Replace All.

Now a confirmation box will appear showing the number of replacements. Click OK in this box and close the Find and Replace window.

Now You can see all the blank spaces have been removed from your selected cells.

3. SUBSTITUTE Function to Remove Blank Spaces

You can also use the SUBSTITUTE function to remove blank spaces. Type the following formula in cell B16,

=SUBSTITUTE(B5, " ", "")

Here, the substitute function will remove the spaces from the selected cell B5.

Press ENTER and you will get the text without blank spaces in cell B16.

Drag the B16 cell to apply the formula for all other cells of column B.

4. Remove Blank Space by REPLACE Function

Using the REPLACE function is another way to remove blank spaces. Type the following formula in cell B16,

=REPLACE(B5,1,LEN(B5)-LEN(TRIM(B5)),"")

Here, the LEN function gives the length of the string of cell B5. LEN(B5)-LEN(TRIM(B5) portion gives the number of blank spaces. Finally, the REPLACE function replaces the original text with the string without blank spaces.

Press ENTER and you will get the text without blank spaces in cell B16.

Drag cell B16 to apply the formula for all other cells of column B.

5. Text to Column Features to Remove Blank Spaces

You can also use Text to Column features to remove Blank spaces from a column. First, select the column and go to Data> Data Tools > Text to Columns

After that, a window named Convert Text to Columns Wizard will appear. Select Fixed width and click on Next.

In the second step, move the vertical line to the end of your text and click on Next.

In the final step, select General and click on Finish.

Now you can see, cells of your select columns have no blank spaces.

6. VBA to Remove Blank Spaces

Another way to remove blank spaces is making a custom function using Microsoft Visual Basic Applications (VBA). First, press ALT+F11. It will open the VBA window. Now in the VBA window from the Project panel right click on the sheet name. A dropdown menu will appear. Click on Insert from the dropdown menu to expand it and select Module.

Now, a Module(Code) window will appear.

Insert the following code in the Module.

Sub Remove_Blanks()

Dim x As Range

For Each x In Selection.Cells

x.Value = VBA.Trim(x.Value)

Next x

End Sub

After inserting the code, close the VBA window. Now, select your dataset and go to View > Macros.

A Macro window will appear. Press on Run.

It will remove all the unnecessary blank spaces from your dataset.

7. Power Query to Remove Blank Spaces

Using Power Query is another method to remove blank spaces. Select your dataset and go to Data > Get Data > From Other Sources > From Table/Range

A Create Table box will appear. Press on OK.

Now, a Power Query Editor window will be opened.

You can see all of your data have been imported in the window.

Now right click on any of the header and select Transform > Trim.

Repeat the same procedure for all the columns. It will remove the blank spaces.

Now from the home tab, select Close & Load.

Now you can see the data is imported in your Excel file in a new sheet named Table.

Download Practice Workbook

Remove Blank Spaces in Excel.xlsm

Conclusion

Removing blank spaces manually can be very tedious. Any of the above described methods will allow you to remove blank spaces from your dataset with a few clicks. I hope you have found this article useful. If you have any confusion please leave a comment.

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

Get FREE Advanced Excel Exercises with Solutions!