Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

In this article, you’ll learn how to remove white space in excel with some easy and useful methods. While working in Excel, the extra space problem is a pretty common issue. Here, I will describe some useful methods that you can use to get rid of white space in Excel. So, read this article carefully to learn all those 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

In this section, I will explain 6 simple and straightforward methods to remove white space in Excel. But before jumping into the methods, let’s be familiar with the data set with which we will work on. Here, I have placed some random employees’ names and their office IDs’.

Employee Names with Extra spaces

As we can see there are many unnecessary spaces in the names of those employees. Now, we will try to remove those white spaces from them. Let’s explore the methods one by one to remove those spaces.


1. Use of Trim Function to Remove White Space in Excel

In this method, we will use the TRIM function to remove white spaces from the names of those employees. To do that, follow the steps below.

Steps:

➤ Firstly, activate cell D5 and type the formula:

=TRIM(C5)

Trim Function to Remove White Space in Excel

➤ Then press the Enter button. As a result, you will get the following result.

using TRIM function to remove white spaces

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

Autofilling the data by using Fill Handle

Read More: How to Remove Spaces in Excel with Formula (5 Quick Ways)


2. Apply the ‘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. To do that, follow the steps below.

Steps:

➤ Firstly, press Ctrl+H to open the Find and Replace dialog box.

➤ Then, click the Space key twice in the Find What bar.

➤ Additionally, keep the Replace with the 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

Read More: How to Remove Blank Spaces in Excel (7 Ways)


3. Use of 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. Follow the steps below.

Steps:

➤  Firstly, type the given formula in cell D5:

=SUBSTITUTE(B5," ","")

SUBSTITUTE Function to Remove White Space in Excel

➤ Then, hit the Enter button. As a result, you will get the following result.

Use of SUBSTITUTE Function to Remove White Space in Excel

➤ After that, use the AutoFill option to copy the formula for the rest of the cells.

using Fill Handle to autofill the rest of the cells

Read More: How to Remove Spaces in a Cell in Excel (5 Methods)


4. Combining 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. To know more, follow the steps below.

Steps:

➤ Firstly, 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.

➤ As a result, you will get the following result.

Combining TRIM, LEFT, and LEN Functions to Remove Trailing Whitespace.

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

using Fill Handle to autofill the rest of the cells

👇 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}

Read More: How to Remove Trailing Spaces in Excel (6 Easy Methods)


Similar Readings


5. Combining 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: 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. To know more, follow the steps below.

Steps:

➤ By activating Cell D5 write the formula given below-

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

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

➤Then, push the Enter button. As a result, you will get this result.

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

➤ Now, 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}

Read More: Remove All Spaces in Excel (9 Methods)


6. Embedding Excel VBA to Remove White Space

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

Steps:

➤ Firstly, select the cells where you will apply VBA.

➤ Then, right-click your mouse on the sheet title.

➤ After that, select the View Code option from the context menu.

Excel VBA to Remove White Space

➤ As a result, the Visual Basic Editor window will appear.

Opening Visual Basic Editor

➤ After that, 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

Writing VBA Code in Editor

➤ Now, press the Play button to run the codes.

Running VBA Code by pressing Play button

➤ Consequently, a new dialog box named ‘Macro’ will open up. Here, click the Run option.

Excel VBA to Remove White Space

➤   As a result, you’ll see the extra white spaces are removed.

Showing Result of Removing White Space by VBA


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo