Md. Sourov Hossain Mithun
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.
How to Remove White Space in Excel: 6 Easy Methods
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’.
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)
➤ Then press the Enter button. As a result, you will get the following result.
➤ Now, use the Fill Handle icon to copy the formula for the other cells.
2. Apply the ‘Find And Replace’ Tool to Remove White Space in Excel
We can also use Excel Find & Replace feature to find and replace double 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.
➤ And you’ll find that all double spaces are removed now and a notification is showing the operation result.
Read More: How to Remove Blank Spaces in Excel
3. Use of SUBSTITUTE Function to Remove White Space in Excel
Do you want to know how to remove white space in Excel and add another character or an empty string in place of that space?
In this section, I’ll use the SUBSTITUTE function to remove white spaces by substituting them with specified characters. The SUBSTITUTE function replaces text in a given string by matching.
In our modified dataset, there are some extra spaces between the office ID numbers. Now, follow the steps below.
Steps:
➤ Firstly, type the given formula in cell D5:
=SUBSTITUTE(B5," ","")
➤ Then, hit the Enter button. As a result, you will get the following result.
➤ After that, use the AutoFill option to copy the formula for the rest of the cells.
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))&""
➤ As a result, you will get the following result.
➤ 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}
Similar Readings
- How to Remove Tab Space from Excel
- Remove Space between Rows in Excel
- How to Remove Space after Number in Excel
- How to Remove Leading Spaces in Excel
- How to Remove Space in Excel after Text
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," ","")))
➤Then, push the Enter button. As a result, you will get this result.
➤ Now, to copy the formula for the other cells just 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}
6. Embedding Excel VBA to Remove White Space
In this last method, we’ll see how to remove white space in Excel using VBA codes. 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.
➤ As a result, the Visual Basic Editor window will appear.
➤ 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
➤ Now, press the Play button to run the codes.
➤ Consequently, a new dialog box named ‘Macro’ will open up. Here, click the Run option.
➤ As a result, you’ll see the extra white spaces are removed.
Download Practice Book
You can download the free practice Excel template from here and exercise on your own.
Conclusion
I hope after reading this article, you now know how to remove white space in Excel. Feel free to ask any questions in the comment section and please give me feedback.
