Masking Social Security Numbers in Microsoft Excel
Periodically in Excel you may need to share sensitive data such as an employee list. Social Security Numbers are often a key element of such lists, but you don't necessarily need to share the entire number. In this article you'll see how to use Excel's TEXT function to mask the first 5 digits of a Social Security Number and only show the last 4 digits as clear text.
We'll use a technique known as concatenation to combine two different pieces of text together. The first piece of text is three asterisks, a dash, two asterisks, and a second dash. These characters will mask the first 5 digits of a Social Security Number. We'll then use the RIGHT function in Excel to extract the last 4 digits of the Social Security Number. The RIGHT function extracts text from the right-hand side, thus the formula =RIGHT(A2,4) will extract only the last 4 characters or digits from cell A2.
There are two ways to combine these two pieces of text together. Many users rely on Excel's CONCATENATE worksheet function, which may result in a formula such as:
The CONCATENATE function is used to combine pieces of text together. It has been replaced by the CONCAT function in the Office 365 versions of Excel, which would shorten the formula to this:
However, the CONCAT worksheet function is not backwards compatible with Excel 2013 and earlier. Nor is it compatible with the perpetually licensed versions of Excel. Office 365 is a subscription-based licensing program, and Microsoft is favoring subscribers with advance access to features that will appear in the next formal version of Excel, which has been dubbed Excel 2019 and is slated for release in mid-2018.
Fortunately, there's a third approach that not only works across all Excel versions, but is simpler to boot. Simply use the ampersand character between the pieces of text, like this:
If you wish to permanently alter Social Security Numbers in this fashion, copy the formulas you created to the clipboard, and then right-click on your selection and choose Paste Special. Then, double-click Values within the Paste Special Dialog box. This will convert the formulas to values.
Otherwise, if you wish to have both a formula-based masked column and the original data then there are a few protective steps you should consider:
- Select the cells that contain formulas.
- Press Ctrl-F1 or click the Alignment Settings button in the corner of the Alignment section of Excel's Home menu.
- Click the Hidden checkbox on the Protection tab of the Format Cells dialog box, and click OK.
- Right-click on the column that contains the full Social Security Numbers and choose Hide.
- Click the Protect Sheet command on Excel's Review menu, and assign a password.
Keep in mind that this offers a limited level of protection. Spreadsheet passwords can be cracked, and data in hidden columns can easily be viewed by way of formulas that reference the hidden columns. Converting the masked numbers to values as described earlier offers the best level of protection.