How to Remove Leading Spaces in Excel

Any work you do in Excel is built around data. The cleaner the data is, the easier worksheets are to read and maintain – and correct formatting plays a big role. Being able to remove leading spaces in Excel helps make your data look clean and keeps your formulas operating as expected.

Extra leading spaces usually make their way into worksheets accidentally (for example, importing a CSV file). Luckily, they’re pretty simple to eliminate. Here are some different ways to do it!

Use the TRIM function to remove leading spaces in Excel

The TRIM function in Excel eliminates the unnecessary leading, trailing, and excess (double) spaces from text strings.

Let’s say you have a column of cells filled with fun Excel facts, like the example below. 

To use the TRIM function, start by entering the following formula in cell C2:

=TRIM(B2)

Then, use the Excel fill handle to drag that formula down through the rest of the table. The range in this example is C2:C6. 

The Excel TRIM function will automatically eliminate all of the cell’s leading and trailing spaces. 

Once you have cleaned the cells, you can Copy/Paste Special Values to replace the original cells if desired.

Remove non-breaking leading spaces in Excel

A non-breaking space (NBSP), also known as a needed space, hard space, or fixed space, is a space character that stops an automated line break from occurring at its position. Non-breaking spaces keep two words on the same line regardless of screen width or other factors.

They typically pop up in your worksheets when you’ve imported data that contains non-breaking space characters. A non-breaking space can be created in Excel with CHAR(160). The TRIM function doesn’t remove these, but the SUBSTITUTE function can. If a text string in B2 contained a non-breaking space, and you wanted to replace it with a normal space, you can use this:

=SUBSTITUTE(B2,CHAR(160)," ")

If you wanted to remove a non-breaking space (instead of replacing it with a normal space):

=SUBSTITUTE(B2, CHAR(160), "")

If you wanted to TRIM a cell and replace non-breaking spaces with normal spaces, you could combine the TRIM and SUBTITUTE functions like this:

=TRIM(SUBSTITUTE(B2,CHAR(160)," "))

Enter that into a cell on your worksheet, and you’ll see the excess spaces are removed and non-breaking spaces have converted to normal spaces.

Note: if you wanted to insert a non-breaking space, you would use the CHAR(160) like this:

="Hello" & CHAR(160) & "world"

Remove leading spaces in Excel using Find & Replace

You can also use Find & Replace to quickly find and delete (or change) double spaces in your cells.

1. To start, select all the data you’d like to check for extra spaces.

2. Use the Ctrl + F shortcut to pull up the Find dialog box. Move to the Replace tab, and type two spaces in the Find what box. In the Replace with box, type nothing to remove all double spaces. If you want to replace double spaces in the middle of a sentence with a single space, enter only one space in the Replace with section.

3. Click Replace All, and Excel will tell you how many replacements were found.

Similarly, you can remove all spaces in your data by typing a single space in the Find what box, and then nothing in the Replace with box. This usually comes in handy when you have sets of numbers or dates that don’t need spaces at all.

How to get rid of line breaks

A line break can be entered manually into a text string by typing Alt+Enter. This forces the text to flow to the next line at that point.

If you want to remove such line breaks, start by selecting all the cells you want to fix.

Use Ctrl + F to pull up the Find dialog box. Select the Replace tab, and in the Find what section, type Ctrl + J. This should display a blinking dot.

Note that on Mac, you’ll use Ctrl + Option + Return.

Leave the Replace with section empty, click Replace All, and you’ll be all set!

Removing leading spaces is one great way to clean data in Excel and make your spreadsheets look professional and consistent.

Do you have any other tips for getting rid of unnecessary spaces or formatting? Let us know in the comments!