CODEWITHSUNDEEP
excelexcel-formula
Holo
Holo

Reputation: 597

Anonymize Excel cell contents

Is there a way (without VBA) to change the content of a cell in Excel so that nobody can see a client name? So for example in a list of cells I may have:

Smith
Jones
Williams
etc...

I want to set up the cell so that when the inputter types in the client name, they can see it to make sure it's correct but on pressing return or moving away from the cell it then anonymizes it so it looks like this:

**ith
**nes
*****ams

Or something similar. Once anonymized the original name cannot be viewed.

Thanks

Upvotes: 0

Views: 4239

Answers (2)

Vityata
Vityata

Reputation: 43565

Without VBA:

=CONCATENATE(REPT("*",(LEN(A1)-LEN(A1)/2+MOD(LEN(A1),2))),
             RIGHT(A1,LEN(A1)/2+MOD(LEN(A1),2)))

It concatenates two parts:

  • half of the length is presented as * through the REPT function
  • the second half of the length is left. The MOD(LEN(A1),2) part is needed to support even and odd length of the strings.

With VBA

Judging from your question, you need something like a login form. The best way to do it is:

  • make UserForm (with VBA);
  • add a TextBox;
  • in the properties of the TextBoxc set the PasswordChar to * or anything else;

enter image description here

Upvotes: 3

Paul Eatwell
Paul Eatwell

Reputation: 3

Copy all of your names to another sheet, then remove duplicates. On the de-duped list, put a code or fake name (Adam Zapple, Barb Dwyer etc.) next to each original name. Then use VLOOKUP on your original data to pull through each code/fake name. Copy and paste the VLookup column as a value then delete the original name column.

Upvotes: 0

Related Questions