Encrypt Cells in a Worksheet

[mbuenger]

I am trying to create a spreadsheet that contains personal sensitive information, in this case, Social Security Numbers. Individuals will be asked to enter information, including their SSN. However, I want the SSN to be automatically encrypted once the user fills in the number. The number would show as “****-**-****” instead of the actual number. Much like when you supply passwords when logging into website and such. What options do I have and how do I apply to the workbook?

Example of table layout:
[TABLE="class: outer_border, width: 500"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Position[/TD]
[TD]Region[/TD]
[TD]SSN[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jack[/TD]
[TD]Salesman[/TD]
[TD]West[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Jill[/TD]
[TD]Salesman[/TD]
[TD]North[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Tom[/TD]
[TD]Salesman[/TD]
[TD]South[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Jerry[/TD]
[TD]Salesman[/TD]
[TD]East[/TD]
[TD]***-**-****[/TD]
[/TR]
</tbody>[/TABLE]

 

[V_Malkoti]

Sounds more like masking values and not encryption. You can use a combination of value format and data validation for that.

For example if I select range A2:A8 and specify custom format type:
"***-**-****";"***-**-****";"***-**-****";"***-**-****"
and on same cells, apply data validation with custom formula:
=AND(LEN(A2)=11, LEN(SUBSTITUTE(A2, "-", ""))=9,NOT(ISERROR(VALUE(SUBSTITUTE(A2, "-", "")))))

I get following output in my spreadsheet:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Masked[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]***-**-****[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]***-**-****[/TD]
[/TR]
</tbody>[/TABLE]

 

[mbuenger]

I explored the cell formatting and it works for what it is worth, but any user in the spreadsheet can still see the number by going to the formula bar. I need a solution that completely masks the number altogether until the owner uses a password or other method to unmask. Any other ideas?

 

[HereForExcel]

Hi. I don't know where my prior response is, but I would like to ask you to please break down this formula and how it works and why you chose AND over OR.
Thanks!

 

[V_Malkoti]

You can set column cells as Locked and then password protect the sheet (uncheck "Select locked cells" option) so that user cannot click on the column to see the values.

 

[V_Malkoti]

Hi. I don't know where my prior response is, but I would like to ask you to please break down this formula and how it works and why you chose AND over OR.
Thanks!

The data validation formula is checking the value for 3 things:

1. Length of input value is 11 characters (including hyphens)
2. Length of input value is 9 characters (excluding hyphens)
3. The value obtained after removing hyphens should be completely numeric. (just my thought)

All three of these conditions must be true for the input value to qualify as valid input value; therefore AND function is applied. Applying OR would mean the value is valid if even one of the conditions is true.

 

[HereForExcel]

The data validation formula is checking the value for 3 things:

1. Length of input value is 11 characters (including hyphens)
2. Length of input value is 9 characters (excluding hyphens)
3. The value obtained after removing hyphens should be completely numeric. (just my thought)

All three of these conditions must be true for the input value to qualify as valid input value; therefore AND function is applied. Applying OR would mean the value is valid if even one of the conditions is true.

Thanks for the response.

I understand number one, but I don't see why this can't be an "OR". I'm not a guru (hence, why I joined this forum), but doesn't making it an AND prevent you from getting a TRUE if it has to meet both conditions of 11 characters AND 9 characters. Does Excel know that you mean including or excluding hyphens because of the SUBSTITUTE formula.

And the NOT part is good for what exactly?

 

[V_Malkoti]

NOT is to check if converting value to a number causes an error (because of non-numeric characters).

And it is AND which ensures that each condition must be true to evaluate to true. Applied SUBSTITUTE formula removes all hyphens in the value leaving (hopefully) only the numbers. Here are some values as example:


[TABLE="class: grid, width: 310, align: left"]
<tbody>[TR]
[TD]Input[/TD]
[TD]With AND[/TD]
[TD]With OR[/TD]
[/TR]
[TR]
[TD]123-46-448a[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]554-87+8965[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]12345687900[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]132-56-5587-[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]456-784-455[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

 

[HereForExcel]

Thank you. I will try this when I get home.