How to Remove Blank Spaces in Excel (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

dataset

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.

trim

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

remove blank spaces

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

apply in all cells

Read More: How to Remove Space Before Text in Excel


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

find and replace

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

find and replace box

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

confirmation box

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

remove blank spaces

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.

SUBSTITUTE FUNCTION in Excel

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

remove blank spaces in Excel

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

remove blank spaces in all cell


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.

REPLACE FUNCTION IN EXCEL

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

REMOVE BLANK SPACES IN EXCEL

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

REMOVE BLANK SPACES

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

TEXT TO COLUMNS

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

TEXT TO COLUMN WIZARD

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

TEXT TO COLUMN WIZARD

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

REMOVE BLANK SPACES IN EXCEL

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

REMOVE BLANK SPACES IN EXCEL

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.

VBA

Now, a Module(Code) window will appear.

MODULE

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

CODE

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

MACRO

A Macro window will appear. Press on Run.

MACRO BOX

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

REMOVE BLANK SPACES IN EXCEL

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

TABLE

A Create Table box will appear. Press on OK.

CREATE TABLE

Now, a Power Query Editor window will be opened.

POWER QUERY

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

DATA IN POWER QUERY

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.

REMOVE BLANK SPACES IN EXCEL

Now from the home tab, select Close & Load.

IMPORT FROM POWER QUERY

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

Remove blank spaces in EXCEL

Download Practice Workbook

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.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo