How-to

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

by XLTools team
April 26, 2022
6 min read
no comments

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:

Leading spaces in a raw dataset in Excel

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.

How to use TRIM function to remove leading spaces in Excel

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.

    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.

Done, cells are clean

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

Raw dataset in Excel is now clean from leading spaces

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.
Tags:
DataPrep ExcelFunctions
Any questions or comments?
Related articles:
Useful habits in Excel: three online Office apps | Excel Productivity Blog
Useful habits in Excel: three online Office apps | Excel Productivity Blog
March 18, 2016
7 min read
Transitioning from VSTO and VBA to Web Add-Ins and JavaScript
Transitioning from VSTO and VBA to Web Add-Ins and JavaScript
December 11, 2024
10 min read
Be the first to know