次の方法で共有

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.

Suzanne

Comments

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