Is there a way to anonymize a list of names by replacing the letters with asterisks in Excel?

Question

I have a list of names in a column e.g.:
Bob Adam Smith, Steve Jobs, Stacy Jones

I’d like to use these names for a case study presentation, but they have to be anonymized.

I imagine something like:
B@b A@@m S@@@h, S@@@e J@@s, S@@@y J@@@s

But with asterisks instead of @.

Some people have very long and very short names or some have middle names, so I’m not sure if it’s possible with Excel formulas.

Something like: “=RIGHT(A1,2)&”**** ****”&RIGHT(A3,2)”

Gives me: Bo**** ****th

Which is no good.

To truly anonymize, the result would have to have nothing in common with the original names, this includes the number of characters etc... Just replace all names with a set number of asterisks *5 or 6 would probably be good. If you need to differentiate between anonymized names, then use numbers (individual 1, or person 1 are good options) — cybernetic.nomad, Nov 25 '22 at 15:30

score 0 · Accepted Answer · answered Nov 25 '22 at 16:07

0

=LET(t,TEXTSPLIT(A1," "),
     e,LEN(t)-(--ISNUMBER(FIND(",",t))),
TEXTJOIN(" ",,
         LEFT(t,1)
         &REPT("@",e-2)
         &RIGHT(t,1+ISNUMBER(FIND(",",t)))))

answered Nov 25 '22 at 16:07

P.b

8,293
2
10
25

score 0 · Answer 2 · answered Nov 25 '22 at 16:09

You could create hash values for the names - those are really anonymous.

Study my article: Hashing in VBA using the Microsoft NG Cryptography (CNG) API

It's a lot of code, but dead easy to implement:

AnonymousName = Hash(FullName)

Full code at GitHub: VBA.Cryptography

Is there a way to anonymize a list of names by replacing the letters with asterisks in Excel?

2 Answers2