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

While working in Excel, the extra space problem is a pretty common issue. In this article, you’ll learn how to remove white space in excel with some easy and useful methods.


Download Practice Book

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


6 Easy Methods to Remove White Space in Excel

Method 1: Use Trim Function to Remove White Space in Excel

Let’s get introduced to our dataset first. Here, I have placed some random employees’ names and their office IDs’.

Trim Function to Remove White Space in Excel

Now I’ll insert some extra spaces beside their first and last names and will show how to remove them with the TRIM function. The TRIM function is used to normalize all spacing.

Step 1:

➤ Activate Cell D5 and type the formula:

=TRIM(C5)

➤ Then press the Enter button.

Trim Function to Remove White Space in Excel

Step 2:

➤ Now use the Fill Handle icon to copy the formula for the other cells.


Method 2: Apply ‘Find And Replace’ Tool to Remove White Space in Excel

Now we’ll use the Find and Replace tool to remove double white spaces beside the names.

Steps:

➤ Press Ctrl+H to open the Find and Replace dialog box.

➤ Click the Space key twice in the Find What bar.

➤ Keep the Replace with bar empty.

➤ Then press Replace All.

‘Find And Replace’ Tool to Remove White Space in Excel

And you’ll find that all double spaces are removed now and a notification is showing the operation result.

‘Find And Replace’ Tool to Remove White Space in Excel


Method 3: Use SUBSTITUTE Function to Remove White Space in Excel

In our modified dataset, there are some extra spaces between the office ID numbers. In this section, I’ll use the SUBSTITUTE function to remove white spaces. The SUBSTITUTE function replaces text in a given string by matching.

Steps:

➤ Type the given formula in Cell D5:

=SUBSTITUTE(B5," ","")

➤ Hit the Enter button.

SUBSTITUTE Function to Remove White Space in Excel

Then use the AutoFill option to copy the formula for the rest of the cells.


Method 4: Combine TRIM, LEFT, and LEN Functions to Remove Trailing Whitespace.

Now I’ll do the operation using the combination of TRIM, LEFT, and LEN functions. The LEFT function in Excel returns the specified number of characters from the start of a string. And the LEN function is a text function in excel that returns the length of a string/text.

Steps:

➤ In Cell D5, type the given formula and hit the Enter button-

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

TRIM, LEFT and LEN Functions to Remove Trailing Whitespace.

Finally, use the Fill Handle tool to copy the formula.

👇 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: Combine CLEAN, TRIM, And SUBSTITUTE Functions to Remove All Spaces from a Cell in Excel

Here, we’ll use another combination of functions to remove extra white spaces and those are the CLEAN, TRIM and SUBSTITUTE functions. The CLEAN function takes a text string and returns text that has been “cleaned” of line breaks and other non-printable characters.

Steps:

➤ By activating Cell D5 write the formula given below-

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

➤ Push the Enter button then.

CLEAN, TRIM, And SUBSTITUTE Functions to Remove All Spaces from a Cell in Excel

To copy the formula for the other cells just use the Fill Handle tool.

CLEAN, TRIM, And SUBSTITUTE Functions to Remove All Spaces from a Cell in Excel

👇 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: Embed Excel VBA to Remove White Space

In this last method, we’ll see how to use Excel VBA codes to remove white spaces.

Step 1:

➤ Select the cells where you will apply VBA.

Right-click your mouse to the sheet title.

➤ Select the View Code option from the context menu.

A VBA window will appear.

Excel VBA to Remove White Space

Step 2:

➤ Write the codes given below:

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 codes.

A new dialog box named ‘Macro’ will open up.

Excel VBA to Remove White Space

Step 3:

➤ Click the Run option.

Excel VBA to Remove White Space

And you’ll see the extra white spaces are removed.


Conclusion

I hope all of the methods described above will be handy enough to remove white space in excel. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

How to Remove Specific Characters in Excel ( 5 Ways)

How to Remove Blank Cells in Excel (10 Easy Ways)

How to Remove Non-Printable Characters in Excel (4 Easy Ways)

Remove Last Character from String with VBA in Excel (2 Easy Ways)

How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Excel is an amazing softwear. Here i will post excel related useful articles. I am a graduate from Bangladesh University of Engineering and Technology. I love to learn new things and work with it. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo