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.

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.

dataset

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

=TRIM(A5)

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

TRIM

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

REMOVE LEADING SPACE

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

REMOVE LEADING SPACE

Read more: How to Remove Space Before Text in Excel

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

REPLACE

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

REMOVE LEADING SPACE

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.

conformation box

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

data

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

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

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

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 “”.

replace and len

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

remove leading space

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


Similar Readings:


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

=SUBSTITUTE(C5, " ", "")

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.

substitute

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

remove leading space

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

remove leading space

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.

vba window

It will open a Module (Code) window.

module

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.

code

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

macro

A Macro window will appear. Click on Run.

macro run

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

remove leading space

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

leading space 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

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo