Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Remove Leading Spaces in Excel (4 Easy Methods)

While creating a large worksheet it is very much possible to have some extra spaces in the contents. To make an excellent worksheet it is needed to remove extra spaces. In this article, I’m going to explain how to remove leading spaces in Excel. In the following picture, you can see an overview image of how we can remove leading spaces from a given data set.

The overview image of removing leading spaces


Download Practice Workbook


4 Ways to Remove Leading Spaces in Excel

In this section, I will explain 4 simple and straightforward methods to remove leading spaces in Excel. To make this explanation more effective for you I’m going to use a sample dataset that has 3 columns representing a person’s full name along with first name and last name. These columns are First Name, Last Name, and Full name. Here to generate the Full Name from the First Name and the Last Name I used the CONCAT function.

Dataset with leading spaces

Now, we will apply the 4 methods one by one to remove leading spaces. So let’s start our journey.


1. Using TRIM Function to Remove Leading Spaces

You can use the TRIM function to remove leading spaces, though the TRIM function not only removes leading spaces but also it removes trailing and double spaces.  To  know more, follow the steps below.

Steps:

  • Firstly, select the cell where you want to place your resultant value. I selected cell E4.
  • Secondly, type the following formula in the cell or into the Formula Bar.
=TRIM(D4)

Using TRIM function

  • Finally, press ENTER. Now, it will show the result by removing all extra spaces.

Using TRIM function

Using Fill Handle to get result for rest of the cells


1.1 Removal of Leading Spaces from Text Values

If you want to remove only the leading spaces from a text then you can use the TRIM function with the MID function and the FIND function. The fusion of these functions will calculate the position of the first text character of a string. Here I also used the LEN function to calculate the string length. To know more, follow the steps below.

Steps:

  • Firstly, select the cell where you want to place your result. I selected cell E4
  • Secondly, type the following formula in the cell or into the Formula Bar.
=MID(D4,FIND(MID(TRIM(D4),1,1),D4),LEN(D4))

Using MID,FIND,LEN & TRIM functions to remove only leading spaces

Here, I’ve inserted D4 as the value where I wanted to remove spaces.
The FIND portion will detect the position of the first text character string then the LEN function will count the string length that is to be extracted through the outer MID function.
  • Finally, press ENTER. Now, it will show the result by removing only the leading spaces of a selected cell.

Using TRIM function to remove only leading spaces

  • Now, if you want you can use the Fill Handle to AutoFill formula for the rest of the cells.

Using Fill Handle to get result for rest of the cells


1.2 Removal of Leading Spaces from Numeric Values

The TRIM function works for numeric values but there is a problem. It converts the number into a text string. So, if you want to remove only the leading spaces from a numeric value then you can use the TRIM function with the VALUE function. For illustration,  I’m using a dataset where it contains numeric values of ZIP Code along with City Name.

Numbers with leading spaces

Now, follow the steps below to remove the leading spaces from those numbers.

Steps:

  • In the first place, select the cell where you want to place your resultant value. So, I selected cell D4.
  • Then, type the formula in the cell or into the Formula Bar.
=VALUE(TRIM(C4))

Removal of leading spaces using VALUE & TRIM Function

Here, the TRIM function removes the leading spaces from the numeric value in C4. Then VALUE converts it into the Number Format.
  • At last, press ENTER. Eventually, it will remove the leading spaces of the numeric value and will convert it into a number format.

Removal of leading spaces using VALUE & TRIM Function

  • At this time, if you want you can use the Fill Handle to AutoFill formula for the rest of the cells.

Use of Fill Handle in Removal of leading spaces using VALUE & TRIM Function

Read more: How to Remove the Trailing Spaces in Excel


2. Using TRIM within SUBSTITUTE to Remove Non-breaking Leading Spaces

In case you derived some values from a website that is made up of the character CHAR (160) then there is a possibility of coming non-breaking spaces with values. The TRIM function doesn’t properly work with non-breaking spaces. For that, you can use the SUBSTITUTE function to remove non-breaking leading spaces with normal space characters. For illustration,  I’ve taken the following sample data of the Book with Author Name.

Books with Authors name containing leading spaces

Now follow the steps below to remove the leading spaces from the dataset.

Steps:

  • Firstly, select the cell where you want to place your resultant value. I selected cell C4
  • Then, type the following formula in the cell or into the Formula Bar.
=TRIM(SUBSTITUTE(B4, CHAR(160), ""))

Using TRIM within SUBSTITUTE to Remove Non-breaking Leading Spaces

Here, the SUBSTITUTE function will substitute all instances of CHAR(160) with normal space characters from the B4 cell. Then the TRIM function will remove those spaces.
  • Lastly, hit the ENTER key. Eventually, it will show the result by removing all non-breaking spaces.

Using TRIM within SUBSTITUTE to Remove Non-breaking Leading Spaces

  • Finally, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Using fill handle for Using TRIM within SUBSTITUTE to Remove Non-breaking Leading Spaces


Similar Readings:


3. Using FIND and REPLACE Tool to Remove Leading Spaces

You also can use the FIND & REPLACE command to remove spaces. At first, we will see how we can remove double spaces. Then we will see an example of removing single spaces.


3.1. Removal of Double Leading Spaces

In this sub-method, we will see an example where we will remove leading double spaces and replace them with single spaces using Find & Replace Tool. Follow the steps below.

Steps:

  • Firstly, select the cell from where you want to remove the leading spaces. Then, go to the Home tab.

Selecting the cells and going to Home Tab

  • From here, go to Find & Select >> Replace…

Selecting Replace Option from Home tab

  • As a result, a dialog box will pop up. There you will need to put how many spaces you want to remove in Find what. Here, I gave double spaces. After that, you will need to put in how many spaces you want to replace the existing spaces in Replace with. Here, I gave single spaces. After putting the spaces in both Find what and Replace with,  click Replace All. 

Filling up the labels in Find & Replace dialoge box

  • Consequently, It will pop a message that how many replacements occurred.

Replacing double spaces with single space.

  • Here it will show 8 replacements. In case there exist more leading spaces in your selected cell then you can click on Replace All again. It will remove the existing leading spaces. Again it replaced 7 spaces.

Replacing double spaces with single space.

  • As a result, all the leading spaces are replaced with a single space.

Replace spaces Using FIND and REPLACE


3.2. Removal of Single Leading Spaces

If you want to remove only single leading spaces then you also can use the FIND & REPLACE command. To do that, follow the steps below.

Steps:

  • First, select the cells from where you want to remove leading single spaces. Then open the Find & Replace dialogue box following the steps shown in the previous section.

Opening Find & Replace Dialgue box

  • Now, from the dialog box, insert a single space in the Find what and keep the Replace with blank.

Filling up data in Find and Replace dialogue box

  • Finally, click on Replace ALL. It will pop up a message showing how many replacements occurred. Now, click OK.  Here it will show 24 replacements.

Replacing single spaces

  • Here, all the single spaces are replaced by no space.

Output after running Find & Replace command


4. Using VBA for Removing Leading Spaces

To remove leading spaces in Excel you also can use Visual Basic. To know more, follow the steps below.

Steps:

  • First, open the Developer tab, then select Visual Basic.

Opening VBA editor

  • Then, it will open Microsoft Visual Basic for Applications.
  • Now, open Insert >>  Module.
  • Creating New Module in VBA EditorHere, the Module is open.

Visual Basic Module

  • After that, write the code to remove leading spaces in the Module.
Sub Remove_Leading_Space()
Dim Rng As Range
Dim SelectedRng As Range
On Error Resume Next
ExcelTitleId = "Removing Leading Space"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Range", ExcelTitleId, SelectedRng.Address, Type:=8)
For Each Rng In SelectedRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

Write the code in Visual Basic Module

Here I’ve taken two variables Rng and SelectedRng, named the dialog box Removing Leading Space then used a For loop to TRIM each selected cell.
I’ve used the VBA LTRIM function to trim.
  • Finally, Save the code and go back to the worksheet.
  • Now, first, select the cell or cell range to apply the VBA. Then type Alt+F8 to open the Macro window. Now, select the Macro then click Run. 

Running Macro to remove spaces

  • Again, a dialog box will pop up showing the selected range then click OK.

Choosing ranges to remove leading spaces by macro

  • Now, the leading spaces of the selected cell range will be removed.

Result After removal of leading spaces by VBA macro

N.B: Things to remember, VBA code will remove the leading spaces only in the selected cell range, and it will change original data and does not support Undo. Furthermore, save a copy of your data before applying this VBA code. 


Practice Section 

I’ve given a practice sheet in the workbook to practice these explained ways to remove leading spaces. You can download it from the above.

Practise Yourself sheet 1

Practise Yourself sheet 2


Conclusion

I tried to explain 4 easy and quick ways to remove leading spaces in Excel. These different ways will help you to remove leading spaces from both text and numeric values. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo