How to Remove Space before Text in Excel (5 Easy Ways)

One thing that often irritates a lot of Excel users is when you have spaces before the text in a cell.

While it may look harmless, these could be quite annoying when you’re using these cells in formulas.

For example, if you’re comparing two columns in Excel, a cell that contains the name “John doe” and the other one that contains the same name with a space character before it “ John Doe” would be considered different.

Unfortunately, a lot of data that many Excel users get have these leading spaces (i.e., a space before the content in the cell starts).

Sometimes this could be because a certain database has been formatted in such a way that the output from it has leading spaces, and sometimes it could be because of manual data entry.

Whatever the case, you need to know how to remove these spaces before text or numbers in Excel.

In this tutorial, I’m going to show you a couple of simple methods that you can use to quickly remove these leading spaces before text.

Table of Contents

Remove Extra Spaces Using the TRIM Function

The fastest way to remove any space characters before the text would be by using the TRIM function.

The TRIM function takes the cell reference as the input argument and removes any leading, trailing, or double spaces in the text in that cell.

Below I have a data set of names in column A, and you can see that there are additional space characters before the name (as well as in between the first and the last name and even after the name in some cases).

Here is the formula that I can use to remove all the unwanted space characters from the cells:

=TRIM(A2)

To use this formula, enter this formula in cell B2, and then copy-paste for all the other cells.

TRIM function to remove spaces from text

Note that this formula would not only remove the space characters from the left of the text in the cell, but it would also remove any additional double space characters in between two words, as well as any trailing space characters (i.e., space character after the text).

In most cases, this is the desired output, and using the TRIM function is the right way to do it.

But if you are in a situation where you only want to remove the space characters from the left of the text, and not remove any of the additional spaces in the middle of the words or after the text, then you cannot use the TRIM function.

TRIM + CLEAN Formula to Remove Non-Printable Characters

In some cases, you may have space characters before the text that are not removed by the TRIM function.

One common scenario when this may happen is when you have non-printable characters before the text.

Non-printable characters are numbers 0 to 31 in 7-bit ASCII code, which usually show up as space characters (but are not considered space characters by Excel).

This is often the case when you get your data from some databases that include them in the download or from web downloads (HTML files).

So, if you’re not able to remove the space characters using the TRIM function alone, you can use the TRIM + CLEAN function combination:

=TRIM(CLEAN(A2))

TRIM and CLEAN function to remove space from text

While the TRIM function would take care of all the regular space characters, the CLEAN function would take care of all the non-printable characters.

Also read: How to Add Text to the End of all Cells in Excel

Remove Spaces Using Find and Replace

Find and Replace is a great tool to find space characters before the text and remove them with a single click.

Below I have some names in column A, and you can see there are varying lengths of space characters in each cell.

In some cells, I do not have any space characters before the name. And in some cells, I have one or more than one space characters.

Below are the steps to use Find and Replace to remove all the space characters in one go:

Select all the names (range A2:A12 in this example)
Hold the Control key and press the H key. This will open the Find and Replace dialog box (you can also get the same by clicking on the Home tab, then clicking on the ‘Find and Select’ option in the Editing group, and then clicking on ‘Replace’)
In the ‘Find what’ field, enter one single space character

Leave the ‘Replace with’ field empty

Click on the ‘Replace All’ button

The above steps would remove all the space characters in all the selected cells.

Find and Replace result with spaces removed

Note that in our example, we only had one continuous string of text, and we wanted to get rid of all the space characters anywhere in the cell (be it before the text or after the text).

In case you have multiple words in a cell, you won’t be able to use this technique as it would also remove the space character between the words.

But if you have single-word text strings (such as first name only or email address) or numbers and you want to get rid of all the space characters, you can use this method.

You can also use the above steps to remove all the double space characters while leaving the single space characters intact. To do that, you need to insert two space characters instead of one single space character in Step 3.

Also read: How to Remove First Character in Excel?

VBA Code to Remove Space Before Text

Using VBA macro code is another easy way to quickly remove space characters before the text in Excel.

Excel VBA has three inbuilt TRIM functions:

TRIM – This would remove any space characters before or after the text
LTRIM – This would remove any space character before the text
RTRIM – This would remove any space character after the text

Below is the VBA code, that would remove although space characters before the text from the selected cells:

'This code is written by Steve Scott of https://spreadsheetplanet.com
'This code will remove all the extra spaces from the left of the text/number in selected cells

Sub RemoveSpaces()
For Each cell In Selection
  cell.Value = LTrim(cell)
Next cell
End Sub

Note that I have used the LTRIM function in the above code, so it would only remove the space character before the text, and it would not remove any additional space characters in between the words or after the text.

The above code goes through each cell in the selection using the For Next loop.

Within the loop, it checks each cell and applies the LTRIM function to it. In case there are any space characters before the text, these would be removed, else nothing happens.

You can also use the Trim or RTrim function as well (where Trim would remove leading and trailing spaces and RTrim would only remove spaces after the text)

There are a couple of ways you can execute this VBA code.

You can put this in a module in your VB editor. This way, you will be able to select any range of cells and run this VBA code by using a keyboard shortcut (which you will have to assign), by running the code through the VB editor, or by adding this code to the Quick Access Toolbar so you can run it by clicking on it.

Adding the Code to a Module

Below are the steps to add this code to the VB editor module:

Click the Developer tab
Click on the Visual Basic icon

In the VB Editor that opens, select any object in the workbook where you want to run this code (i.e., any of the worksheet names or ThisWorkbook object)

Click on the Insert option, and then click on Module. This will insert a new module for that workbook

Copy the above code and paste it into the Module code window

Copy paste the code in the module code window

Click on the Save icon in the toolbar to save the code

The above steps would make the VBA code available in the workbook in which you have copied and pasted it.

To use this code, select the range of cells from which you want to remove the space character before the text, and then run this code by opening the VB Editor, clicking any line in the code, and hitting the F5 key (or clicking on the green play icon in the toolbar)

Note that once you have the code in the workbook, you need to save it as a macro-enabled file (with a .XLSM extension).

Running the Code Using the Immediate Window

The above method of copy-pasting the code in the Module code window allows you to keep the code so that it could be reused multiple times in the same workbook.

But if you just want to use the code once, there’s a faster way to do it (using the Immediate Window).

Below are the steps to run this code using the Immediate Window:

Click the Developer tab
Click on the Visual Basic icon

In the VB editor that opens, check if you can see the Immediate Window. If you don’t, click on the View option and then click on the ‘Immediate Window’ option

Type (or copy-paste) the below code in the immediate window

For Each cell In Selection: cell.Value = LTrim(cell): Next cell

Place the cursor at the end of the line of code

Place cursor at the end of the code line

Hit the Enter key

When you place the cursor at the end of the code line and hit the enter key, that code is executed.

Note that the above line of code has three parts that are separated by a colon (you can’t have multiple lines of code in the immediate window but you can keep multiple lines while separating them with colons).

This is essentially the same code that uses a For Next loop to go through each cell in the selection and apply the LTRIM formula in each cell.

The good thing about this immediate window method is that it’s quick and does not require you to insert a module or save the file as a macro-enabled file.

All you need is this single line of code that you can use in any Excel workbook, and once you have used it to remove the space character before the text, you can close the VBA editor.

So if you need to use this code just once, you can use the immediate window method.

And in case you want to use this code multiple times in the same workbook or different workbooks, then you can use the subroutine method shown above.

Also read: How to Remove the Last Digit in Excel?

Remove Spaces Using Power Query

Power query already has inbuilt functionality to remove spaces before and after the text.

So if you’re already using Power Query as a part of your workflow, or if you’re working with large data set where you have to combine multiple workbooks/worksheets, or connect with multiple different data sources and fetch the data from there, you can use the Power Query method to clean the data and remove space before and after the text.

Remember that you need to have your data in an Excel Table if you want to use it in Power Query.

Below I have some data in column A, and I want to remove all the space characters before the text in each cell in the column:

Here are the steps to do this using Power Query:

Select any cell in the data (which must be an Excel Table)
Click the Data Tab
In the ‘Get & Transform Data’ group, click on the From Table/Range icon. this will open the Power Query editor

Select the column from which you need to remove the spaces. In our example, since we just have one column, we don’t need to do this (but if you have more than one column, then you need to select the column first)
Click on the ‘Transform’ tab

Click on the ‘Format’ option
Select the Trim option from the drop-down menu. Doing this would instantly remove the spaces before and after the text in the selected column

Click the Trim option in the Format drop down

Click the Home tab
Click on Close and Load

The above steps would insert a new sheet where you would have the data from which space characters have been removed.

When you use the Trim option Power Query, it will only remove the space characters before and after the text, but it would not remove any additional spaces between words in the text.

One amazing thing about Power Query is that now your output result (whether space characters have been removed before and after the text) is connected to your original data source.

So now, if you make any change in the original data source (or you copy-paste a new data over this original data), you don’t need to repeat the Power Query steps to remove the space characters before and after the text.

You can simply right-click on the output result (the one we got in a newly inserted sheet) and then click on refresh.

Power Query will work in the back end to redo all the steps and give you the new result within seconds.

In this example, we had our data in an existing table in the workbook. But you can also connect Power Query to any workbook or database and fetch the data from it, transform it in Power Query, and get the result.

And once the process/steps are set, you can just refresh and get the result whenever your original data changes.

Also read: Extract Last Word in Excel

Some Third-Party Add-ins to Remove Spaces

Apart from the methods that I’ve covered already, you can also use some third-party plugins that make it quite easy to remove space characters in Excel.

One such useful third-party add-in that you can use is Ablebits Ultimate Suite for Excel.

While you can do everything using the inbuilt Excel functions and functionalities, having a third-party add-in makes it quite easy for beginner Excel users (who do not have the know-how on how to use functions or VBA or Power Query).

Third-party add-ins such as these offer a lot of amazing functionalities that are either not available in Excel or have a longer workaround.

So these are some of the ways you can use to quickly remove space characters before text or numbers in Excel.

I hope you found this Excel tutorial helpful.

Other Excel tutorials you may also find useful: