How to Remove Extra Spaces from Excel

An easier way to clean up your Excel spreadsheet

Ted French

When you import or copy text data into an Excel worksheet, you often end up with extra spaces. Using Excel's TRIM function, or a shortcut key to trigger Excel's search and replace feature, you can easily remove those unnecessary spacing.

In the case of the TRIM function, you'll need to create a new column in the worksheet for your clean text. Then you can copy and paste the clean text back into the original column.

A much faster trick is to use the CTRL-H shortcut key to search the problem column and quickly remove all of the extra spaces.

How the TRIM Function Works

The syntax for the TRIM function is:

=TRIM(Text)

The Text, in this case, refers to the data with the extraneous spacing. This argument can be:

  • The actual text enclosed in quotation marks
  • A cell reference to the location of the text data in the worksheet

Many people think that the TRIM function removes only the spaces at the beginning and the end of the text. In actuality, it gets rid of all extra spaces.

For example, if the text in cell A1 is:

This is a really bad sentence.

The Excel function =TRIM(A1) will provide the following result:

This is a really bad sentence.

Using the TRIM Function

To remove extra spaces with the TRIM function, follow this procedure:

  1. Click on cell A6 to make it the active cell – this is where the function will go
  2. Type =TRIM(A4)
  3. Press Enter
  4. The text from A4 with extra spaces will appear in A6, but with all of the extra spaces removed.

The problem at this point is that both A4 and A6 exist in the sheet when you only want the clean text (in A6) to replace the messy text (in A4).

Replacing the content from A6 to A4 is simple to accomplish:

  1. Right-click on A6.
  2. Select Copy
  3. Right-click on A4.
  4. Select Paste Special
  5. Select Values and then click OK.
  6. Delete the text from A6.

Now only the clean text, without spaces, exists on the sheet.

If you're using Excel Online, disregard steps 4 and 5 above. Instead, click the down arrow under Paste on the Home ribbon, then select Paste Values.

Removing Extra Spaces From an Entire Column

The method above works well if you're only cleaning spaces from one cell, but what if you want to remove spaces from an entire column?

The procedure for dealing with a column isn't that different from dealing with one cell. In this example, the column with too many spaces starts with A2.

  1. Create a new empty column beside the column you want to remove spaces from (in this example this new column starts with B2).
  2. Click on the top empty cell in your new column (B2).
  3. Type =TRIM(B2) and press Enter.
  4. Hold down the Shift key, and hover your mouse at the bottom of cell B2 until the cursor changes to two horizontal lines with an arrow above and below them. (This feature isn't available in Excel Online; you'll have to use the Fill Handle to copy the formula.)
  5. Double-click the right mouse button to auto-fill the entire column

Now you'll have column A with the extra spaces, and column B with the same text cleaned up.

Perform the same copy and paste (values only) procedure in the previous step, but copy and paste the entire columns rather than just individual cells.

Finally, right click at the top of column B to highlight the entire column, and delete it, shifting all other cells on the spreadsheet to the left.

Using the Search and Replace Short Cut Key

If the text in your column has extra spaces within the text, not right before or right after, you can quickly clean that text by using Excel's Search and Replace feature.

  1. Select the entire column you want to clean
  2. Press the CTRL-H shortcut to open Excel's Search and Replace window
  3. In the Find what field, type two spaces
  4. In the Replace with field, type one space
  5. Keep pressing the Replace All button until all extra spaces are gone

The benefit of using this approach is that you don't have to go through the entire copy-and-paste procedure that you need to with the TRIM function.

The method you use to remove extra spaces in Excel depends on what your original data looks like, and where the spaces are.

Either way, one of these two methods should work well to help you clean up that messy text in your spreadsheet.