If you happen to have a Worksheet that contains Social Security Numbers, you are most surely looking for a method to protect those numbers by only displaying the last four digits. We are all very aware of Identity Theft these days and strive to protect ourselves and our clients in that regard. So instead of displaying 123-45-6789, you would only want to display 6789.
Basically, in order to do this depends largely on whether the Social Security Number you stored in the cell in your Worksheet as a number or as text.
If the Social Security Number is entered with its dashes (as shown above), Excel will store it as text. If the Social Security Number is entered without dashes (i.e., 123456789) then Excel will store it as a number.
Now, if the Social Security Number is stored as a number, you could be tempted to create a custom format that will hide the first part of the number. But guess what, there is no way that I know, how to do this with a custom format. You could create a custom format that would hide all except the first digits, (i.e., 000, , “-** – ****”).
As you may surmise, from the example above, custom formats do not allow you to mask out anything except the last portion of any value. Another problem with using this method, however, is that Excel rounds the Social Security Number, so that the number would be displayed as 123-**-****.
So, the best solution for displaying only the last four numbers of a Social Security Number is to use a second column for the actual display. Rather than trying to form the number itself, it is best to use a formula that refers to the number and creates the result you want. Suppose the Social Security Number is in cell B, then you would place the following formula in a different cell:
=”***-****- ” & RIGHT (B8, 4)
This should work with any Social Security Number regardless of whether it is stored as a number or as text. The other benefit to this method is that it will allow you to completely hide the original numbers. Even if you were able to use a custom format to hide the first portion of the number (which, as I told you above, you cannot), a person could still see the Social Security Number in the Formula bar if the cell containing the number were selected.
Using the formula method though, allows you to hide the source column or use sheet protection to hid the contents of the column, which is a huge benefit if your goal is to protect the Social Security Number from others.
I hope this has been helpful to you!