How to Remove Leading Space in Excel (5 Useful Ways)

Sometimes there may be some extra spaces at the front of your data in Excel and you may need to remove these extra leading spaces to use this data. In this article, I’ll show you five easy ways to remove leading space in Excel.

Table of Contents hide

Download Practice Workbook

5 Ways to Remove Leading Space in Excel

1. Using TRIM Function to Remove Leading Space

2. Utilizing Find and Replace Feature to Remove Leading Space

3. Applying REPLACE and LEN Functions to Remove Leading Space

4. Remove Leading Space by Inserting SUBSTITUTE Function

5. Incorporating VBA to Remove Leading Space

Conclusion

Download Practice Workbook

Removing Leading Space.xlsm

5 Ways to Remove Leading Space in Excel

Let’s say we have a dataset of sales records where different cells have leading spaces.

Now, we will remove this leading space using five different ways using this dataset.

1. Using TRIM Function to Remove Leading Space

Using the TRIM function is the easiest way to remove any kind of unnecessary space from a data cell.

Primarily, type the formula in any empty cell (B13).

=TRIM(B5)

Afterward, press ENTER and you will get the data without any leading space in cell B13.

In the formula, B5 is the data cell from where the leading space will be removed.

Further, use the Fill Handle to apply the same formula for other cells in your dataset.

Read More: How to Remove Space Before Text in Excel

2. Utilizing Find and Replace Feature to Remove Leading Space

Using Find and Replace command is another way to remove leading space from your dataset.

Firstly, select the cells from where you want to remove the leading spaces.
After that, go to Home > Editing > Find and Select > Replace.

Afterward, a Find and Replace box will appear.
Further, insert one single space in the Find what box and click on Replace All.

Consecutively, you will see a Microsoft Excel box will appear which shows the number of replacements.
Lastly, click OK on this box and close the Find and Replace box.

Finally, you will see that all the leading spaces from your selected cells have been removed.

Read More: How to Remove Spaces in a Cell in Excel

3. Applying REPLACE and LEN Functions to Remove Leading Space

You can remove the leading space by using the REPLACE function and the LEN function altogether.

Primarily, type the following formula in an empty cell (B13),

=REPLACE(B5,1,LEN(B5)-LEN(TRIM(B5)),"")

Afterward, press ENTER and you will get the data without any space in cell B13.

In the Formula, B5 is the data cell. The LEN function gives the total length of the string. LEN(B5)-LEN(TRIM(B5))portion detects the number of characters we want to replace and the REPLACE function removes the leading space with “”.

Finally, apply the same formula for other cells or use the Fill Handle to copy the formula to remove the leading space from other cells.

Similar Readings

4. Remove Leading Space by Inserting SUBSTITUTE Function

You can also remove the leading space by using the SUBSTITUTE function.

Firstly, Type the following formula in an empty cell (B13),

=SUBSTITUTE(B5, " ", "")

Then, press ENTER and you will get the data without any space in cell B13.

In the formula, B5 is the data cell from where the leading space will be removed. " ", "" within the formula indicates that all the spaces will be removed from the data cell.

Finally, apply the same formula with a different cell references or use the Fill Handle to copy the formula to other cells and remove the leading spaces from them.

5. Incorporating VBA to Remove Leading Space

You can use Microsoft Visual Basic for Application (VBA) to create a macro that will remove all the leading spaces.

Firstly, press ALT+F11 to open the VBA window.
Then, right-click on the sheet name from the left panel of the VBA window.
Afterward, click on Insert to expand, and select Module.

Instantly, it will open a Module (Code) window.
Afterward, insert the following code in that window.

Sub RemoveLeadingSpace()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "RemoveLeadingSpace"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

In the code, we have created a Sub Procedure RemoveLeadingSpace, that you can apply to a range of cells. The code will execute the VBA.LTrim function to your selected cells and will remove all the leading spaces.