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.

Answer 1

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

Answer 2

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