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
2 Answers
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 theREPTfunction - 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
TextBoxcset thePasswordCharto*or anything else;
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.
