Formula Watch: Hide customer data with CONCATENATE

Privacy is an important watchword these days, and Excel can help you hide sensitive customer information such as Social Security Numbers or credit card numbers.

Simply use CONCATENATE to combine a generic text string representing the missing digits with the last four digits. That's enough information for, say, a customer service representative to confirm an identity without sharing the full number to employees who don't need access to it.

To do this, use the formula like so:

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

In the example, the text string "***-**-" is appended with the 4 rightmost characters in cell A2:

Once you're done, you can Hide Column A:

Now, to lock it, go to the File menu in Excel 2010 and choose Protect Workbook, Protect Current Sheet:

Set a password and the workbook should be protected against unhiding the column:

But the data is still there if you need it and have the password.

Suzanne

Comments

• Anonymous
  January 18, 2011
  It is a combination of basic operations, can be broken but still very good. Thanks, Ahmad