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

Get FREE Advanced Excel Exercises with Solutions!

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.

remove leading space in excel preview


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.

Dataset

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.

using TRIM function

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.

result-remove leading space in excel

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.

Selecting Find & Replace feature

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

Setting find & replace dialog box

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

closing notification

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

remove leading space in excel- result using find & replace

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.

applying REPLACE & LEN functions

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.

remove leading space result using replace and len functions


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.

Inserting SUBSTITUTE function

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.

remove space in excel result using substitute function


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.

inserting new module in vba window

  • 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

incorporating vba code

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.
  • After inserting the code, close the VBA window.
  • later on, select the cells of your dataset and go to View> Macros > View Macros.

opening macro

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

macro run

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

range selection in custom box

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

remove leading space in excel result using vba


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 Bala
Prantick Bala

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo