How to Remove Extra Spaces From Excel

An easier way to clean up your Excel spreadsheet

Screenshot of Excel with cursor over Values

What to Know

  • Create new column in Excel for clean text. Use TRIM function to copy and paste clean text into original column.
  • Ex.: Type =TRIM(A4) into cell A6 > Enter. Right-click A6 > Copy > Right-click A4 > Paste Special > Values > OK.
  • You can also use the CTRL+H key to search the problem column and quickly remove all of the extra spaces.

This article explains how to use Excel's TRIM function and the CTRL+H shortcut key to remove unnecessary spacing from your worksheet.

How to Use 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.

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.

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.

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.

Was this page helpful?