Scrub Social Security Numbers in Excel in 10 Simple Steps

Whether payroll professionals use the most current Excel in Microsoft 365 or an earlier version, they’ll never find enough time to use all of the functions and shortcuts available to them. Even expert users of Excel will stumble upon a “new” or better way to import and export data from time to time.

Some Excel features touted as a cure-all to common snags won’t solve a payroll professional’s underlying problem. A common example of that involves employee Social Security numbers (SSNs). Oftentimes, some SSNs aren’t numerical data at all — instead, they’re text that appears in a separate column.

That’s because of SSNs with leading zeros. Excel hates leading zeros and automatically truncates them, as any beginning learner of Excel will discover. For example, inputting 01234 will appear as 1234 in the cell. And SSNs leading with zero have been assigned to millions of citizens born in New York and New England (Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island and Vermont) since SSNs’ rollout in the 1930s.

Excel’s Number format lets you put dashes back into SSNs. The catch is that this only works for appearance’s sake. To preserve leading zeros and ensure data integrity, SSNs should be stored as text rather than as numeric values in Excel. If you try importing the numbers into an HR software program, for example, the leading zeros still won’t appear. That’s because they’re not there — you inadvertently skipped important steps needed to convert text to numerical data.

To clean SSNs, follow these procedures

David Ringstrom, an Excel spreadsheet trainer and consultant, shared the best way to clean up employee SS numbers and additional problem-solving topics in a recent Premier Learning webinar, Excel For Payroll Professionals: Improve Accuracy & Efficiency.

Here are the steps Ringstrom recommends Payroll pros use to clean up SSNs:

1. Select all employee SSNs
2. Click on the Data tab
3. Select Text to Columns. This will allow you to manipulate SSN data in different columns
4. Convert Text to Columns Wizard – Step 1 of 3 will appear. Text to Columns Wizard will tell you if your data is Delimited or Fixed Width. (Hidden benefit: Text to Columns Wizard automatically converts text to number values.) Now click Finish
5. Click on the Home tab. You’ll see the Number box appear (underneath the non-highlighted Data tab)
6. In the Number box, click on the format button, which is located in the bottom right corner of the box. This will bring up the Format Cells box
7. In the Format Cells box, select Special under the Category: column
8. Then select Social Security Number under the Type: column
9. Once both are selected, click OK, and
10. You should now see one column of scrubbed, numerical data SSNs.

Result: Payroll can now highlight and import employee SSNs much more easily. Finance staffers will be able to see all the data in one column.