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.

dataset

Download Practice Workbook

7 Ways to Remove Blank Spaces in Excel

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

Read more: How to Remove Spaces in a Cell in Excel

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


Similar Readings:


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

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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo