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.

Download Practice Workbook

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).

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

• 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),

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

• 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

• Remove All Spaces in Excel (9 Methods)
• How to Remove Extra Spaces in Excel (4 Methods)
• Remove Trailing Spaces in Excel (6 Easy Methods)

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),

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

• 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

• After inserting the code, close the VBA window.
• later on, select the cells of your dataset and go to View> Macros > View Macros.

• Consecutively, a Macro window will appear.
• Further, click on Run.

• After that, a box named RemoveLeadingSpace will appear. You can see the range of the selected cells in the box. Click OK.

• Finally, the macro code will be executed on the selected cells. As a result, all the leading spaces will be removed.

Conclusion

You can remove the leading space in Excel by using any of the above-described methods. If you face any type of confusion, please leave a comment.

Further Readings

• How to Remove Blank Spaces in Excel (7 Ways)
• Remove the Trailing Spaces in Excel (2 Easy Ways)