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 line breaks in Excel.
How to Remove Leading Spaces in Excel?
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.
Example #1
Consider a sheet that 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 the 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 (9,601 ratings)
View Course
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. I hope you understand how the spaces will affect the formula and how to find whether spaces are there or not.
Example #3
If space is there in the beginning and space is thereafter now, we will see if the space is available in between two words.
If we observe, there is space between every word, and I have also given the length for those two names. We will add one more space in between the names then we will see whether Vlookup will give results.
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”. I hope you understand how spaces are creating errors while working with formulas in excel.
Example #4 – 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 types of spaces between A and B.
In the first line, only one space is given between A and B under the Alphabet 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 the “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 the name “After trim” has A and B in the same way in all the 3 cells, this is because the Trim function removes the additional spaces between A and B. The length of the column after the 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 the Trim function, we cannot remove entire spaces between the words except additional spaces.
Example #5 – Removal of All Spaces in Excel
As we are unable to remove all spaces using Trim, we can use the 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 the trim is 7.
Now apply the 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 the substitute function in the below screenshot.
As per the 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 were just given double quotes without space.
Follow the same steps for other cell and check the length of the results from the substitute; it is 6 for both the cells, which means there is no space available in both the cells.
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 the “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.
- The trim function helps remove all the additional spaces, but it will not remove 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 the 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 a downloadable excel template. You can also go through our other suggested articles –
