How to remove leading spaces in Excel [Step-by-Step Guide]

The problem of leading spaces

There may be times when you want to remove leading spaces from a text string in Excel. These leading spaces can come about when you enter data into a cell that doesn't have the proper formatting.

One of the biggest problems with spaces is that they are often invisible to the human eye. Of course, you can sometimes spot a couple of them here and there, but it's impossible to catch all of them if you're working with thousands of records.

Fortunately, there are a few different ways to remove leading spaces in Excel so that your data is clean and easy to work with. This article will show you how to do that in less than 60 seconds.

Getting started

Here's what our running example looks like:

Those leading spaces prevent us from using those values correctly in formulas and calculations, so let's get rid of them!

Method #1: the TRIM function

The TRIM function is a built-in function in Excel designed to remove leading and trailing spaces from a text string. To use it, simply enter the following formula into a cell:

For example, if you wanted to remove leading spaces from the text in cell A1, you would use the following formula:

Let's test the function in action to see how it can handle leading spaces.

Works like a charm each and every time.

Pro tip:Double-click the fill handle to execute the function for the remaining values in column A.

Method #2: XLTools

The main issue with using the TRIM function is that it's a little tedious. Once the function is executed, you have to manually pull the modified data and replace the original values with it.

If you have a couple of columns to clean up, that's not a big deal. However, if you operate with thousands of rows and columns, the task at hand can become a mind-numbing ordeal even for some of the most resilient data enthusiasts.

1. Select the entire worksheet (or the range of cells you want to clean up).

2. Click the XLTools tab.

3. Select the Data Cleaning drop-down menu.
4. Choose Clean cells.

   

   

   How to remove leading spaces in Excel with XLTools

5. In the dialog box that pops up, check the leading and trailing spaces checkbox.

6. Click OK to close out.

   

   

   Clean cells from leading and trailing spaces

Once there, XLTools will scan through the specified data range and clean up any leading and trailing spaces.

Ta-da! You just got rid of all of the leading spaces plaguing your worksheet in less than 10 seconds.

Impressive? You bet! But there's so much more to what XLTools can do for you. In addition to being able to quickly clean up your data, XLTools will also give you access to over 300 different Excel productivity tools and features.