How to Remove Leading Spaces in Excel

Has it happened with you that you export some data from some system and there are unwanted spaces before a number, text in Excel? Removing these spaces one by one manually is a very time-consuming and irritating task that no one would like to do ideally. Then how to remove these unwanted leading spaces before a word in Excel? In this blog, we would unlock the technique to remove the leading spaces before a word in the excel worksheet. This can be done using both the Formula and Non-Formula-based approaches. This blog aims at unlocking both the ways.

Let us take a small example to perform the same.

Suppose we have a list of words. The words have blank space before them as shown in the screenshot below.

The requirement here is to remove the space before these texts and get the result as shown below :

There are three ways using which we can remove the extra space before any word in excel :

1. =TRIM() Formula
2. =SUBSTITUTE() Formula
3. Using the “Find and Replace” Option (without formula)

Let us understand each of the methods in detail. 

Remove Leading Spaces Using formula =TRIM()

The Formula =TRIM() is used to remove multiple spaces between words in a sentence. 

Use the below formula (refer to the screenshot below).

=TRIM(A2)

You can now see that the formula removed the extra spaces at the beginning of the word. 

Copy this formula to other cells in column B.

Remove Leading Spaces Using =SUBSTITUTE() Formula

This is also a formula-based approach. We would be using =SUBSTITUTE() formula to replace the spaces before a word/text in an excel.

However, it is pertinent to note that, unlike =TRIM(), this formula will not be useful for removing additional spaces between two words in a sentence. 

Enter the following formula in cell B2 :

=SUBSTITUTE(A2,” “,””)

Explanation on the above formula: The formula would first take the value of cell A2 (denoted by the first attribute). After that, it would find spaces in that value (denoted by the second attribute- ” “). And finally, the excel would replace the ” ” (i.e. space) with “” (i.e. blank) which is denoted by the third attribute of the formula.

Copy this formula to other cells in column B.

Using “Find and Replace” Option (without Formula)

This is the most simple method to remove additional spaces before any word in an excel. Unlike the above two methods, this is not a formula-based approach.

Follow the below steps to remove the spaces in the beginning of the text:

Select the cells which contain the text (from which spaces are to be removed)

Open the “Find and Replace” dialog box by pressing Ctrl+H on your keyboard.

In the “Find What” section of this dialog box, just press the space button on your keyboard. This denotes that Excel should search/find for spaces in the selected cells.

And do not enter anything in the “Replace with” section of this dialog box. Keep it as it is. This denotes that the value to be replaced with is blank/none.

Now press the “Replace All” button.

As a result, you can see that all the spaces at the beginning of the text get removed and you have a result as below :

This brings us to the end of this blog. Share your views and comments in the comment section below.