How to Remove Leading Spaces in Excel?

When working with Excel, you may encounter situations where unwanted leading spaces appear in your data.

Leading spaces refer to spaces that are added at the beginning of text strings or numbers in cells.

These spaces can make it difficult to analyze and manipulate your data, as they may affect sorting, filtering, and calculations.

In this tutorial, we will cover five techniques to remove leading spaces in Excel.

Issues with Leading Spaces in Excel

Leading spaces in Excel can cause unwanted effects such as the following:

  • Aesthetics: Leading spaces in cells can make a worksheet look untidy and unprofessional.
  • Sorting: If you have leading spaces in cells, they can affect the results of sorting functions. Excel treats spaces as characters, so cells with leading spaces may not sort correctly.
  • Formulas: If you have formulas referencing cells with leading spaces, they may not work as intended. For example, a formula that counts a range of cells may not include cells with leading spaces, resulting in an incorrect calculation.

Types of Spaces in Excel

There are two main types of spaces you may encounter in Excel:

  • Regular spaces: These are the typical spaces you create using the spacebar on your keyboard. They are denoted by a single blank character.
  • Non-breaking spaces: These spaces are special characters that prevent text from wrapping to the next line. They are created when you press the keyboard combination Ctrl+Shift+Space or when copying and pasting data from other sources, such as the web.

Non-Breaking Spaces vs. Regular Spaces

Understanding the difference between non-breaking spaces and regular spaces is essential for proper formatting in Excel.

As mentioned earlier, regular spaces are created with a single press of the spacebar, whereas non-breaking spaces require a specific keyboard combination or may be introduced when copying data from external sources.

Non-breaking spaces can cause issues with functions, formulas, and data analysis because they are treated as distinct characters, unlike regular spaces.

Therefore, it is crucial to identify and remove any leading non-breaking spaces to ensure accurate formatting and data manipulation within Excel.

Also read: How to Remove Leading Zeros in Excel?

Method #1: Using the TRIM Function

We can use the TRIM function to remove leading spaces in Excel. The TRIM function deletes all spaces from a text string leaving only the single spaces between words.

Let’s consider the following dataset with the names of ten lakes in the USA. Notice that all the names have leading spaces.

Dataset with leading spaces

We want to use the TRIM function to remove the leading spaces from the dataset.

We use the following steps:

  1. Select cell B2 and enter the below formula:
=TRIM(A2)
TRIM function to remove leading spaces
  1. Drag or double-click the fill handle feature to copy the formula down the column.
Apply the formula to the entire column

The leading spaces are removed from the dataset.

  1. Copy the data without spaces in cell range B2:B11, paste it back in as values, and then delete the original data in column A.

We remain with the dataset without the leading spaces.

Data where leading space has been removed

Note: The TRIM function is designed to remove all leading and trailing common spaces from a text string but cannot remove non-breaking spaces. You must use the method in the following section to remove non-breaking spaces from a text string.

Also, note that since the TRIM function removes leading, trailing, and double spaces from a text string, you cannot use it if you want to remove leading spaces only.

Also read: How to Remove Space before Text in Excel

Method #2: Using TRIM, SUBSTITUTE, and CHAR Functions (Removing Non-breaking Leading Spaces)

If you pull data from a website into Excel, it may come in with non-breaking leading spaces.

Notice that the ASCII code for a non-breaking space is 160, and the ASCII code for a regular space is 32.

Unlike regular spaces, non-breaking spaces are treated as a single unit and are not broken when text is wrapped at the end of a line.

The non-breaking spaces are helpful when dealing with items such as numbers, dates, or names that should not be separated by line breaks but are troublesome in Excel operations such as sorting.

The TRIM function alone cannot remove leading non-breaking spaces.

Let’s consider the following dataset with non-breaking leading spaces.

dataset with non-breaking leading spaces

Let’s try to use the TRIM function to remove the leading spaces, as we did in Method #1:

TRIM function to remove the leading spaces

The TRIM function has not removed the non-breaking leading spaces from the dataset.

To remove the non-breaking leading spaces from the dataset, we can use the SUBSTITUTE function inside the TRIM function to replace the non-breaking spaces with regular spaces.

We use the steps below:

  1. Select cell B2 in the example dataset and enter the formula below:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
SUBSTITUTE function inside the TRIM function
  1. Double-click or drag the fill handle in cell B2 to copy the formula down the column.
drag the fill handle
  1. Copy the data in column B and paste it back as values.
  2. Delete the original data by clicking the letter header of column A to select the column, right-click the selection, and choose Delete on the shortcut menu.
Delete the original column data

We remain with clean data without leading non-breaking spaces:

clean data without leading non-breaking spaces
Also read: How to Remove a Specific Character from a String in Excel

Method #3: Use Flash Fill to Remove Leading Spaces in Excel

We can use Flash Fill to remove leading spaces in Excel. The Flash Fill feature allows us to fill in values in a column based on a pattern or example.

Suppose we have the following dataset showing the names of ten lakes in the USA. Notice that the names have leading spaces.

Dataset with leading spaces

We want to use Flash Fill to remove the leading spaces from the dataset.

We proceed as follows:

  1. Select cell B2 and type in the name of the first lake on the list, “Lake Willoughby, Vermont,” without leading spaces.
  2. Select cell B3 and type in the name of the second lake on the list, “Lake George, New York,” without leading spaces.
  3. Select cell B4 and begin to type in the name of the third lake on the list. Notice that as you start to type, the Flash Fill feature gives suggestions for the following data entries in grey.
begin to type in the name of the third item on the list

Press Enter to accept the suggestions if they are correct.

Press Enter to accept the suggestions

If Flash Fill does not suggest the following entries or the suggestions are incorrect, proceed with the steps below.

  1. Complete typing in the third name on the list and select the next cell C5.
Select cell C5
  1. On the Data tab, click the Flash Fill button on the Data Tools group. Alternatively, you can press the shortcut Ctrl + E.
Click on the Flash Fill icon

The rest of the data are automatically filled in.

rest of the data automatically fills
  1. Delete column A, which has the original data, by clicking its letter column header, right-clicking the selection, and choosing Delete on the shortcut menu.
Delete column with original data

You remain with the clean data without leading spaces:

Data where leading space has been removed

Note: Flash Fill may give incorrect results if your data is complex or there are multiple possible patterns. We recommend that you carefully review the results before accepting them.

Sometimes, you may need to edit the results to correct errors or inconsistencies manually.

Also read: Remove the Last 4 Characters in Excel

Method #4: Using Find and Replace Feature

If the number of leading spaces in each dataset cell is the same, we can use the Find and Replace feature to remove them.

Suppose we have the following list of ten names of lakes in the USA. Each name has three leading spaces.

Dataset with leading spaces

We want to use the Find and Replace dialog box to remove the three leading spaces in each cell.

We use the following steps:

  1. Select the cell range A2:A11 containing the data from which we want to remove the leading spaces.
Select the Dataset
  1. Press the shortcut Ctrl + H to open the Find and Replace dialog box.
  2. Click in the Find what box and press the spacebar on the keyboard three times to enter three spaces. Leave the Replace with box blank and click the Replace All button at the bottom of the dialog box.
Enter three space character in find what

Excel replaces the leading spaces in the dataset with empty strings.

First three spaces are removed

Excel displays a message box indicating the number of sets of the three leading spaces replaced with empty strings.

message box indicating the number spaces removed

Note: This method will only work if there are a consistent number of leading spaces in text strings.  Also, you cannot use this method to remove single leading spaces, as it can also remove other single spaces from the text strings.

Method #5: Using VBA to Remove Leading Spaces in Excel

We can create a sub-routine in Excel VBA and execute it to remove leading spaces in Excel.

For example, suppose we have the following dataset with leading spaces.

names dataset with leading spaces

We want to remove the leading spaces using Excel VBA.

We use the below steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Open the Insert menu and choose Module to insert a module.
Click on Module
  1. Copy and paste the following sub-routine into the module.
Sub RemoveLeadingSpaces()
    ' Declare a variable named rng of type Range,
    ' which will be used to represent each cell in the selected range
    Dim rng As Range

    ' Start a loop that iterates over each cell (rng) in the currently selected range (Selection)

    For Each rng In Selection
        ' Remove leading spaces from the current cell's value
 using the LTrim function
        rng.Value = LTrim(rng.Value)

    ' End the loop and move to the next cell in the selection
    Next rng

End Sub
  1. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
  2. Press Alt + F11 to switch to the current worksheet containing the dataset.
  3. Select the dataset.
Select the dataset
  1. Press Alt + F8 to open the Macro dialog box.
  2. On the Macro dialog box, choose the RemoveLeadingSpaces macro and click Run.
Run the macro

The sub-procedure loops through each cell in the current selection and uses the LTrim function to remove any leading spaces from the cell’s value.

VBA removes the leading spaces

Note: The LTrim function in the code only removes common leading spaces and cannot remove non-breaking spaces; therefore, you cannot use this method to remove non-breaking spaces.

Leading spaces in Excel can be frustrating.

For example, they can lead to incorrect results of sorting functions. Therefore, we should remove the leading spaces to prevent such unwanted effects in data operations.

This tutorial showed five techniques for removing leading spaces in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment