Column A and column B appear to be identical, but they aren’t.  If we did a VLOOKUP function with the contents of column A as the lookup value, and the contents of column B in the table array, we wouldn’t get a single match.  Why?  Because column A has spaces after the last letter of the Product ID, and column B doesn’t.  These are commonly called trailing spaces, and they can wreak havoc in a workbook.

I most often see see trailing spaces in data pulled from databases.  Trailing spaces can prevent data from being summarized correctly in a pivot table, cause spacing problems in a mail merge, and return N/A# in a lookup function.

You can eliminate trailing spaces using the TRIM function.  The TRIM function will remove extra spaces at the beginning of a cell, the end of a cell, or between words in a cell.

Use the TRIM function

The syntax for the TRIM function is:

=TRIM(text)

To eliminate trailing spaces in the contents of cell A2 using the TRIM function, I would enter =TRIM(A2) in a blank cell and press Enter on the keyboard.  To copy the formula down for the rest of my data, I would then double click on the fill handle.

If you want to delete column A or replace the contents of column A with the trimmed text in column B, you’ll need to copy the text in column B and paste the values into column A.

Paste Values in Excel 2007

  1. Copy the data.
  2. Select the place you would like to place it.  It can be the same area you copied.
  3. On the Home tab of the ribbon, click on the bottom half of the Paste button.
  4. Click on Paste Values.

Paste Values in Excel 2003

  1. Copy the data.
  2. Select the place you would like to place it.  It can be the same area you copied.
  3. Go to the Edit menu and select Paste Special.
  4. Select the button next to Values.
  5. Click OK.

For more information about pasting values, read our Combine the Contents of Two Cells post.