How To Work With Social Security Numbers in Excel
HR Tech Tip – How to Work with Social Security Numbers in Excel

HR Tech Tip – How to Work with Social Security Numbers in Excel


Sometimes you can’t avoid managing employee data inside of a spreadsheet.  If you ever have to work with social security numbers in Excel, you’ve likely discovered it can be tricky.

When importing data, the leading zeros may be automatically removed, forcing you to take up precious time to manually edit.  Another scenario: you may need to manually add or remove the dashes.  So in this article, I’m sharing a few hacks that can make your life easier.

But first, please note that in all of the following examples, I am assuming that the list of social security numbers is in column A.  Any other blank column may be used to calculate the formula.  Once a formula has been entered for the first result, the formula can be copied and pasted all the way down the column as needed.

Now onto the shortcuts…

Tip #1 – How to add dashes to a social security number in Excel

This formula is perfect for when your list of social security numbers has been imported into Excel without dashes.  Use this to automatically add in the dashes in the appropriate positions.

LEFT(A1,3)&”-“&MID(A1,4,2)&”-“&RIGHT(A1,4)

Tip #2 – How to remove the dashes from social security numbers in Excel

Working in Excel

If you ever need to remove the dashes, there is a very simple way to do so. Highlight the column containing your social security numbers.  Next, select ‘Replace’ from the ‘Find & Select’ icon in Excel.  When prompted, enter a dash for ‘Find what.’ Leave the ‘Replace with’ field blank.  Final step, select the ‘Replace All’ button and watch the dashes disappear!

Tip #3 – How to Add the leading zeros

If you have social security numbers that have lost their leading zeros and do not need dashes, then this formula is just for you!  The number of zeros in the quotes represents the length of the number you need after the leading zeros are added (in this case, 9 digits).

=TEXT(A1,”000000000″)

Let’s say the scenario is the same but now, you also need to add the dashes.  No worries!  Just use the following formula:

=TEXT(A1,”000-00-0000″)

Once you have your list of numbers in the format you need, you can copy the column with the formulas and then paste them into the correct column choosing ‘Values’ from the paste options.

Have you run into any other issues when working with social security numbers in Excel?  How did you solve them?  Share in the comments.

 

Share This Post