How to Remove Leading Spaces in Excel (4 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.

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.

Sample Dataset

Download to Practice

4 Ways to remove leading spaces in excel

1. Using TRIM Function

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.

Firstly, select the cell where you want to place your resultant value.
➤ I selected the 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

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

Using TRIM function

1.1. Remove 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 use these functions in a formula first select the cell where you want to place your result.
➤ I selected the cell E4
Second, type the following formula in the cell or into the Formula Bar.

=MID(D4,FIND(MID(TRIM(D4),1,1),D4),LEN(D4))

Using TRIM function to remove only leading spacesHere, 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 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 TRIM function to remove only leading spaces

1.2. Remove Leading Spaces from Numeric Values

The TRIM function works for numeric values but there is a problem that 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.

To show you that way I’m using a dataset where it contains numeric values of ZIP Code along with City Name.

Remove Leading Spaces from Numeric Values

In the first place, select the cell where you want to place your resultant value.
➤ I selected the cell D4
Then, type the following formula in the cell or into the Formula Bar.

=VALUE(TRIM(C4))

Remove Leading Spaces from Numeric Values

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.

Remove Leading Spaces from Numeric Values

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

Used FIll Handle to AutoFill Formual

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.

I’ve taken the following sample data of the Book with Author Name.

Sample Dataset to use SUBSTITUTE

First, 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 TRIM within SUBSTITUTE to Remove Non-breaking Leading Spaces


Similar Readings:


3. Using FIND and REPLACE

You also can use the FIND & REPLACE command to remove spaces.

For that, first, select the cell from where you want to remove the leading spaces.
Then, open the Home tab >> from Find & Select >> select the Replace

Using FIND and REPLACE

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.

Using FIND and REPLACE

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.

Using FIND and REPLACE

After putting the spaces in both Find what and Replace with. Then, click Replace All. It will pop a message that how many replacements occurred.
➤ Here it will show 7 replacements.

Replace spaces Using FIND and REPLACE

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 3 spaces.

Replace spaces Using FIND and REPLACE

Here, all the leading spaces are replaced with a single space.

Replace spaces Using FIND and REPLACE

3.1. Single Spaces

If you want to remove only single leading spaces then you also can use the FIND & REPLACE command.

After selecting the cell from where you want to remove the single leading spaces.
Now, open the Home tab >> from Find & Select >> select the Replace

Removing Single spaces Using FIND and REPLACE

A dialog box will pop up from there insert a single space in the Find what and keep the Replace with blank.

Removing Single spaces Using FIND and REPLACE

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.

Removing Single spaces Using FIND and REPLACE

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

Removing Single spaces Using FIND and REPLACE

3.2. Double Spaces

On the other hand, if you want to remove only double leading spaces then you can use the FIND & REPLACE command.

To begin with, select the cell from where you want to remove the single leading spaces.
After that, open the Home tab >> from Find & Select >> select the Replace

Removing Double spaces Using FIND and REPLACE

Now, a dialog box will pop up from there insert double space in the Find what and single space in Replace with.

Removing Double spaces Using FIND and REPLACE

Next, click on Replace ALL. It will pop up a message showing how many replacements occurred. Now, click OK.
➤ Here it will show 11 replacements.

Removing Double spaces Using FIND and REPLACE

Finally, all the double leading spaces are replaced by single leading spaces.

Removing Double spaces Using FIND and REPLACE

4. Using VBA 

To remove leading spaces in Excel you also can use Visual Basic.
First, open the Developer tab >> then select Visual Basic.

Using Visual Basic

Then, it will open Microsoft Visual Basic for Applications.
Now, open Insert >> select Module.

Using Visual Basic
Here, 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.
First, select the cell or cell range to apply the VBA
Then, open the View tab >> from Macros >> select View Macros.

Using Macros to run VBA code

At this time a dialog box will pop up now select the Macro then click Run. 

Using Macros to run VBA code

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

Using Macros to run VBA code

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

Using Macros to run VBA code

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.

Practice Sheet

 

Practice Sheet

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