3 Ways to Remove Leading, Trailing, & Extra Spaces in Excel

Download Article

Use Find & Replace and simple formulas to clean up your Excel data

Written by Nicole Levine, MFA

Last Updated: September 13, 2024 Tested

Download Article

Using Find and Replace

|

Using the SUBSTITUTE Function

|

Using the TRIM Function

|

Video

|

Tips

Sometimes importing data into Microsoft Excel from an external source can add extra spaces to your cell values. This can really mess with your formulas! If your cells are plagued with random spaces between numbers, leading and trailing spaces, and multiple spaces between words, don't worry—there are easy ways to fix your data. This wikiHow article will teach you how to remove spaces between numbers and characters, as well as from before and after values, in your Excel spreadsheet.

Quick Guide

Press Ctrl + H to bring up the "Find & Replace" function.
Press Spacebar in the "Find what" field.
Click "Replace all."
Select "Okay."

Method 1

Method 1 of 3:

Using Find and Replace

Download Article

1
Highlight the range where you want to remove all spaces. For example, if you want to remove spaces from C2 through C30, highlight those cells.
- This method removes any extra spaces between numbers, words, and other characters. To eliminate leading and trailing spaces, see Using the TRIM Function.
- This method will not delete extra space at the beginning or end of the value, just spaces between characters.
Press Ctrl+H to open Find and Replace. This quick keyboard shortcut works on both Windows and macOS.^{[1]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}

Advertisement
Make sure to press it only once. This adds a space to the "Find what" field.
5
Click Replace All. It's the second button at the bottom of the window. This searches for all spaces between numbers and other characters in the selected range and deletes them.
- A pop-up will show how many spaces were removed. Click "'OK'" on the pop-up to return to your worksheet.

Method 2

Method 2 of 3:

Using the SUBSTITUTE Function

Download Article

1
Click the top cell in a blank column. To remove extra space between numbers, characters, or words in a cell, you can use the SUBSTITUTE function. Click a cell on the same row as the first cell in the column with the extra spaces.
- For example, if you want to remove spaces from column A, and A's first row of data is in row 1 (A1), click the first cell in your blank column (e.g., B1, C1, D1, etc.).
3
Click the first cell in the column with spaces. For example, if the first cell in the column that has extra spaces is A1, click it now.
- If you clicked cell A1, the formula should now look like this: =SUBSTITUTE(A1 .
The cell should now look like this: =Substitute(A1,.
5
Type " ",. That's a double quotation mark, a space, another double quotation mark, and then a comma. There's a space between the two sets of double quotes—this is important.
- The formula should now look like this: =SUBSTITUTE(A1," ",.
6
Type "". This time, there's NO space between the sets of quotes.
- The formula should now look like this: =SUBSTITUTE(A1," ","".
7
Type a closed parenthesis ) and press ↵ Enter or ⏎ Return. In the end, the formula should now read =SUBSTITUTE(A1," ",""). You will now see the contents of the selected cell (A1, in this example) without spaces in the new column.^{[2]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- For example, if C2 said w ww . wikih ow .com, your new cell will say www.wikihow.com.
8
Apply the changes to the entire column. The easiest way to do this is to use Autofill:
- Click the cell into which you typed the formula to select it.
- Double-click the square at the bottom-right corner of the box.
- Alternatively, drag the square down until you've reached the bottom of the column.
- You can now easily copy the new column without spaces and paste it into the original column. Your new space-free data is now in place.

Method 3

Method 3 of 3:

Using the TRIM Function

Download Article

The TRIM function removes extra spaces from the beginning and end of a cell's value. It will also remove extra space (except for single spaces) between words.^{[3]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source} Click the first cell on the same row as the first line of data in the column with the extra spaces.
3
Click the first cell that contains extra spaces. This adds the cell address to the TRIM formula.
- For example, if you click A1, your formula should now look like this: =TRIM(A1
Your ending formula should look like this: =TRIM(A1). This removes any excess spaces at the beginning or end of the selected cell. It will also remove extra space between words "except" for single spaces.
5
Apply the changes to the entire column. The easiest way to do this is to use Autofill:
- Click the cell into which you typed the formula to select it.
- Double-click the square at the bottom-right corner of the box.
- Alternatively, drag the square down until you've reached the bottom of the column.
- You can now easily copy the new column without extra space and paste it into the original column. Your new space-free data is now in place.