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

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 @)

The problem is that some people have very long and very short names or some have middle names and others don’t, so I’m not sure if it’s even possible with good old excel formulas.

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

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

Which obviously is no good

I’m open to VBA solutions, but I’m a beginner there, so please play nice.

CodePudding user response：

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

CodePudding user response：

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