Excel Remove Leading Spaces (Table of Contents)
Remove Leading Spaces in Excel
While working in Excel we frequently use the spaces for each word. There are different spaces in excel which we may not observe while working in Excel. Different spaces available are Leading spaces/Trailing spaces, additional spaces and a line breaks in Excel.
How to Remove?
In this article, first, we will see how to identify spaces between cells. These spaces may create a problem while working with formulas in Excel.
We will see a few examples where it causes a problem. We will use formulas and functions to remove spaces in Excel.
Remove Leading Spaces in Excel – Example #1
Consider a sheet has data of student names and roll numbers as below.
Now we will apply Vlookup for the first table to find the Roll numbers from the second table. The results will be like below screenshot after we apply the Vlookup.
You may doubt why we are discussing Vlookup now. Now apply the same Vlookup by adding the spaces before the name and check whether the Vlookup is working or not. Observe the below screenshot, for the names “Kiran” and “Kumar ” we gave spaces hence the roll number is coming as N/A for both student names.
This is because due to the spaces at the beginning of the names. Now we have seen the problem is due to the space available in the beginning.
4.9 (4,804 ratings)
View Course
Remove Leading Spaces in Excel – Example #2
Now we will check if space after the name will cause any problem while applying formulas. We will keep the space after the names “Kiran” and “Rajesh”. Why I am mentioning the names because we cannot identify if space is available after the name.
To know the number of spaces available we can use the formula length and can find. Observe the below screenshot I have applied the LEN formula for the same names in both the tables, but the length is different in both the tables.
In the first table, we have given space after the name hence length is 7 for the same names and in the second table, it is 6. Hope you understand how the spaces will affect the formula and how to find whether spaces are there or not.
Remove Leading Spaces in Excel – Example #3
We see if space is there in the beginning and space is thereafter now we will see if the space available in between two words.
Now if we observe there is space between every word and I have given the length also for those two names. Now we will add one more space in between the names then we will see whether Vlookup will give results or not.
I added the space for “Rachel Green” in the first table and “Virat Singh” in the second table. Because of that two of them giving the results as “N/A”. Hope you understand, how spaces are creating errors while working with formulas in excel.
Remove Leading Spaces in Excel – Example #4
Remove Spaces in Excel Using TRIM Function
The fast and easy way to remove spaces in excel is using the TRIM function. Now we will apply the Trim function and will check the length of the words.
Consider the below table with different type of spaces between A and B.
In the first line only one space is given between A and B under the Alphabets column and the length of that cell is 3 because it has two characters and one space. The second line has three spaces in between A and B and one space after B hence the length of that cell is 6 and the final cell is given multiple spaces before A, in between A and B and after B.
Now apply “Trim” function to remove the additional spaces between A and B. We can observe the formula bar which is marked with an arrow mark. If we observe the column with name “After trim” has A and B in the same way in all the 3 cells this is because Trim function removes the additional spaces between A and B. The length of the column after trim is 3 for all the cells.
If we observe the trim function left only one space between A and B but removes the additional spaces between A and B, before and after A and B. So, with the help of Trim function we cannot remove entire spaces between the words except additional spaces.
Remove Leading Spaces in Excel – Example #5
Removal of All Spaces in Excel
As we are unable to achieve to remove all spaces with the use of Trim we can use substitute function to remove all the spaces in the cell.
Consider the below table where there are spaces available in both the cells however the excel user do not want any spaces.
First, apply the Trim function and check.
Observe the results after applying a trimming function. Still, there is a space between – and 5 in the first row and there is a space between 6 and / in the second row. The length of cells after trim is 7.
Now apply Substitute function and check the lengths. Substitute function is used to replace one text with another text. Here we need to replace space with no space or null. Observe the format of substitute function in the below screenshot.
As per above screenshot in “Text” we selected the cell address as A2 and “Old_text” is space hence given space between double quotes and “new_text” is null hence we just given double quotes without space
Follow the same steps for other cell and check the length of the results from substitute, it is 6 for both the cells which means there is no space available in both the cells.
Remove Leading Spaces in Excel – Example #6
Remove Line Breaks in Excel
In case if your Excel has line breaks, in the same cell there are two different lines as shown in the screenshot below.
Observe “I love” in one line and “dogs” in the next line but belong to the same cell A1. If we want to remove the line breakages like this we can use “Clean” function without removing the additional spaces.
Things to Remember
- Additional spaces in excel will create disturbances while applying formulas in Excel.
- There are different spaces like Leading/Trailing/In between.
- Trim function helps to remove all the additional spaces, but it will not remove if there is only one space between words or letters.
- If we want to remove all the spaces, we can use the Substitute function.
- If there are any line breaks, we can use “Clean” function, but it will not remove additional spaces but just remove line breaks.
- We can also use find and replace to remove spaces.
Recommended Articles
This has been a guide to Remove Leading Spaces in Excel. Here we discussed How to Remove Leading Spaces in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
