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 (SS) numbers. Often times some SS numbers aren’t numerical data at all — instead they’re text that appear in a separate column.
That’s because of SS numbers 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 SS numbers 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 SS’s rollout in the 1930s.
Excel’s Number format lets you put dashes back into SS numbers. The catch is this only works for appearance’s sake. 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 Social Security numbers, 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 SS numbers:
- Select all employee SS numbers
- Click on the Data tab
- Select Text to Columns. This will allow you to manipulate SS data in different columns
- 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
- Click on the Home tab. You’ll see the Number box appear (underneath the non-highlighted Data tab)
- 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
- In the Format Cells box, select Special under the Category: column
- Then select Social Security Number under the Type: column
- Once both are selected, click OK, and
- You should now see one column of scrubbed, numerical data SS numbers.
Result: Payroll can now highlight and import employee SS numbers much more easily. Finance staffers will be able to see all the data in one column.
Free Training & Resources
Resources
You Be the Judge
You Be the Judge
Case Studies