excelvbaexcel-formulaanonymize

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

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.

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