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.

Let’s say we have a dataset of sales records where different cells have leading spaces. Now we will remove this leading space using four different ways.

Download Practice Workbook

5 Ways to Remove Leading Space in Excel

1. 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. Type the formula in any empty cell (A13).

Here, A5 is the data cell from where the leading space will be removed.

Press Enter and you will get the data without any leading space in cell A13.

Drag the A13 cell to apply the same formula for other cells in column A of your dataset.

2. Find and Replace Command to Remove Leading Space

Using Find and Replace Command is another way to remove leading space from your dataset. First, select the cells from where you want to remove the leading spaces. After that go to Home > Editing > Find and Select > Replace

After that a Find and Replace box will appear. Insert one single space in Find what box and click on Replace All.

You will see a Microsoft Excel box will appear which shows the number of the replacement. Click OK on this box and close the Find and Replace box.

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

3. REPLACE and LEN Function to Remove Leading Space

You can remove the leading space by using the REPLACE function and the LEN function altogether. Type the following formula in an empty cell (B13),

Here, B5 is the data cell. 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 “”.

Press Enter and you will get the data without any space in cell B13.

Drag the B13 cell to apply the same formula for other cells in the B column of the dataset.

4. Remove Leading Space by SUBSTITUTE Function

You can also remove the leading space by using the SUBSTITUTE function. Type the following formula in an empty cell (C13),

Here, C5 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.

Press Enter and you will get the data without any space in cell C13.

Drag the C13 cell to apply the same formula for other cells in the C column of the dataset.

5. Using VBA to Remove Leading Space

You can use Microsoft Visual Basic Application (VBA) to create a macro which will remove all the leading spaces. First press ALT+F11 to open the VBA window. Right click on the sheet name from the left panel of the VBA window, click on Insert to expand, and select Module.

It will open a Module (Code) window.

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

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

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

A Macro window will appear. Click on Run.

Now a box Named RemoveLeadingSpace will appear. You can see the range of the selected cells in the box. Click OK.

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.

The post How to Remove Leading Space in Excel (5 Useful Ways) appeared first on ExcelDemy.