Learn How to Remove Extra Spaces from Excel

Make your Excel spreadsheet tidy by removing extra spaces

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 short cut key to trigger Excel's search and replace feature, you can easily remove those extra spaces.

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

A much faster trick is to use the CTRL-H short cut 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 you want to remove spaces from. 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 only removes the spaces at the beginning and the end of the text. It actually removes 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 Clean Spaces

Use the TRIM function to remove extra spaces using the following procedure:

  1. Click on cell A6 to make it the active cell – this is where the function will be located
  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 really only want the clean text (in A6) to replace the messy text (in A4).

This 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.

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 isn't much different. In this example, consider the column with too many spaces starting 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
  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 inside the text, without trailing or leading spaces, 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 CNTRL-H short cut 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 removed

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 really 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.