Extra spaces in your data file, particularly in the SSN column, can result in data errors and should be removed. Spotting an extra space in your data file can be nearly impossible – but fortunately, Excel has a couple of useful tools that can locate and remove spaces for you.


Remove Extra Spaces

If you need to remove extra spaces (spaces at the beginning or end of words, and extra spaces between words), the TRIM function is the simplest method for removing those. This tool is best for locating those tricky extra spaces in a text field (Plan ID, Coverage Type, etc.).


Note: this will not remove all spaces between words or numbers (it will leave single spaces between words, but remove any additional spaces), so if you’re trying to remove ALL spaces (such as within an SSN), you’ll need to skip to the Remove All Spaces guide.

  1. Add a temporary column. I’ve added a column labeled “TRIM”, which will serve as the temporary location for my new data. You’ll notice I’ve hidden all of the data between my actual SSN column and my temporary column. This isn’t necessary, but it will make the process a bit easier. 
  2. Choose the TRIM formula. Select the first field under your “TRIM” column (this should be in row 2), go to the Formulas tab, select “Text”, and locate the TRIM function.
  3. Assign the Source Cell. For the “text” field, click on the original text in your source column.
  4. Copy the Formula to the rest of the column. 
    1. Click on the small square in the lower-right corner of the formula cell.
    2. Drag the formula down until it includes all cells in that column. This will apply the TRIM function to every row.
  5. Highlight and Copy all of the data from the temporary column. Make sure not to copy the column header.
  6. Paste it over the original data, using the Paste Values option. Make sure to start from the top-most data point (should be row 2). DO NOT replace the column header.
  7. Delete the temporary column. If you hid any of your columns, you should unhide those now as well.


Remove ALL Spaces

If you receive an SSN error, but can’t see any issues with the SSN information you’ve provided, it could be that one or more of the SSNs have a space before, after, or in-between the numbers. The SSN field will not accept any spaces, so the TRIM function won’t work here.


To get rid of ALL spaces in a data column, you’ll need to use the Substitute function:

  1. Add a temporary column. I’ve added a column labeled “No Spaces”, which will serve as the temporary location for my new, space-free, data. Just like above, I’ve hidden the extra columns to make the process easier.
  2. Choose the Substitute formula. Select the first field under your “No Spaces” column (this should be in row 2), go to the Formulas tab, select “Text”, and locate the Substitute function.
  3. Set the Function Arguments: 
    1. For the “text” field, click on the original text in your source column.
    2. In the Old_text field, enter “ “ (notice the space between the quotation marks).
    3. In the New_text field, enter just the quotation marks, without a space: “”
    4. Leave the “Instance_num” field blank.
  4. Copy the Formula to the rest of the column. 
    1. Click on the small square in the lower-right corner of the formula cell.
    2. Drag the formula down until it includes all cells in that column.
  5. Highlight and Copy all the data from the temporary column. Make sure not to copy the column header.
  6. Paste it over the original Employee SSN data, using the Paste Values option. Make sure to start from the top-most data point (should be row 2). DO NOT replace the column header.
  7. Delete the temporary column. If you hid any of your columns, you should unhide those now as well.