How to Remove White Space in Excel (6 Easy Ways)

In the sample dataset, we have placed some random employee names and their office IDs. There are many unnecessary spaces in the employee names. Let’s remove those white spaces.

Method 1 – Use the TRIM Function to Remove Extra White Spaces in Excel

Select cell D5 and copy the following formula:

=TRIM(C5)

Press Enter to get the result.

Use the Fill Handle icon to copy the formula for the other cells.

Method 2 – Apply the Find and Replace Tool to Remove White Spaces in Excel

Press Ctrl + H to open the Find and Replace dialog box.
Press the Space key twice in the Find What bar.
Keep the Replace with bar empty.
Press Replace All.

All double spaces are removed with a notification showing how many replacements were made.

Method 3 – Use the SUBSTITUTE Function to Remove White Spaces in Excel

In our modified dataset, there are some extra spaces between the office ID numbers.

Steps:

Copy this formula in cell D5:

=SUBSTITUTE(B5," ","")

Hit Enter.

Use the AutoFill option to copy the formula to the rest of the cells.

Method 4 – Combining TRIM, LEFT and LEN Functions to Remove Trailing Whitespaces.

Steps:

In cell D5, apply the following formula and hit the Enter.

=TRIM(LEFT(C5,LEN(C5)-1))&""

You will get the trimmed result.

Use the Fill Handle tool to copy the formula through the column.

Breakdown of the Formula:

LEN(C5)

It will find the number of characters in Cell C5. And will return as-

{19}

LEFT(C5,LEN(C5)-1)

This function will keep the characters of Cell C5 according to the given length from the beginning of the text. It will return as-

{Alfred Molina }

TRIM(LEFT(C5,LEN(C5)-1))&””

Finally, the TRIM function will remove the extra spaces. Then the result will be as below-

{Alfred Molina}

Method 5 – Combining CLEAN, TRIM and SUBSTITUTE Functions to Remove All Spaces from a Cell in Excel

Select Cell D5 and copy the following formula into it:

=TRIM(CLEAN(SUBSTITUTE(B5," ","")))

Press Enter.

To copy the formula to the other cells, use the Fill Handle tool.

Breakdown of the Formula:

SUBSTITUTE(B5,” “,””)

This function will substitute the extra space with no space. That will return as-

{HL236744}

CLEAN(SUBSTITUTE(B5,” “,””))

The CLEAN function will then clean non printable characters if there are remain any and it will return as-

{HL236744}

TRIM(CLEAN(SUBSTITUTE(B5,” “,””)))

Finally, the TRIM function will trim extra spaces and will return as-

{HL236744}

Method 6 – Embedding Excel VBA to Remove White Spaces

Select the cells where you will apply VBA.
Right-click on the sheet title.
Select the View Code option from the context menu.

The Visual Basic Editor window will appear.

Copy the code given below into the window.

Option Explicit
Sub Trim_Blanks()
Dim s As Range
  For Each s In Selection.Cells
    s.Value = Trim(s.Value)
  Next s
End Sub

Press the Play button to run the code.

Anew dialog box named ‘Macro’ will open. Click the Run option.

The extra white spaces are removed.

Download Practice Book

You can download the free practice Excel template from here and exercise on your own.

How to Remove White Space in Excel.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

How to Remove White Space in Excel (6 Easy Ways)

Method 1 – Use the TRIM Function to Remove Extra White Spaces in Excel

Method 2 – Apply the Find and Replace Tool to Remove White Spaces in Excel

Method 3 – Use the SUBSTITUTE Function to Remove White Spaces in Excel

Method 4 – Combining TRIM, LEFT and LEN Functions to Remove Trailing Whitespaces.

Method 5 – Combining CLEAN, TRIM and SUBSTITUTE Functions to Remove All Spaces from a Cell in Excel

Method 6 – Embedding Excel VBA to Remove White Spaces

Related Articles

Leave a reply Cancel reply