Website Visitor, Here Are Your Articles for Friday, March 16, 2018
Is this email not displaying correctly?
View it in your browser .
Employer's Choice Solutions
Joni Prose
President
jprose@employerchoice.com
(941) 627-0777
Friend Me on Facebook Follow Me on Twitter Connect with me on LinkedIn
Website
About Us
Customer Reviews
Free Quote
Contact Us
Share Save

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:

=CONCATENATE("***-**-",RIGHT(A2,4))

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:

=CONCAT("***-**-",RIGHT(A3,4))

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:

="***-**"&RIGHT(A4,4)

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:

  1. Select the cells that contain formulas.
  2. Press Ctrl-F1 or click the Alignment Settings button in the corner of the Alignment section of Excel's Home menu.
  3. Click the Hidden checkbox on the Protection tab of the Format Cells dialog box, and click OK.
  4. Right-click on the column that contains the full Social Security Numbers and choose Hide.
  5. 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.

 
Share Save

Your Comments

Saved Articles
Comments and Feedback
Refer A Friend
Your Privacy
Our firm provides the information in this e-newsletter for general guidance only, and does not constitute the provision of legal advice, tax advice, accounting services, investment advice, or professional consulting of any kind. The information provided herein should not be used as a substitute for consultation with professional tax, accounting, legal, or other competent advisers. Before making any decision or taking any action, you should consult a professional adviser who has been provided with all pertinent facts relevant to your particular situation. The information is provided "as is," with no assurance or guarantee of completeness, accuracy, or timeliness of the information, and without warranty of any kind, express or implied, including but not limited to warranties of performance, merchantability, and fitness for a particular purpose.
Powered by
Copyright © IndustryNewsletters All rights reserved.

This email was sent to: support@digitaleel.com

Mailing address: 22476 Sacramento Ave, Port Charlotte, FL 33954