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:

Example of CONCATENATE in use

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

Hiding a column

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

Protect Current Sheet

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

Protect Sheet

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


Comments (1)

  1. Ahmad Al-Musallami says:

    It is a combination of basic operations, can be broken but still very good.