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

To show you the ropes, we prepared a sample company dataset. If you quickly need to come up with raw data to practice with, just look up any B2B data provider that offers a free company dataset (list of tools here).

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:

=TRIM(cell reference)

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

=TRIM(A2)

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.

Fortunately, there's an easier way. The XLTools Add-In for Excel makes it possible to quickly and easily remove leading and trailing spaces from your data with just a few clicks.

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.

   

   

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

6. Click OK to close out.

   

   

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.

To see what else it can do for you, start a free trial and see it for yourself.

For more advanced projects — from VBA to financial modeling — reach out to seasoned Excel consultants.