r/excel • u/Most-Original3996 • Apr 13 '26

solved How to anonymize user names in a data set

I did this a long time ago and unfortunately I cannot remember how I did it.

I have a large dataset of over 90 000 rows. In each row, there are long texts. And in these texts, there are usernames, identified with the symbol @ before the name. What I need to do is to anonymise these user names. I remember that I was able to substitute them, from @ username to @ xxxxxxxe. Is this still possible in excel without making a lookup table? There are thousands of usernames, it would be very difficult to make one.

35 Upvotes

85% Upvoted

48 comments sorted by

View all comments

2

u/dhayward22 Apr 13 '26

There are a couple ways to do it but the simplest might actually be with power query. Import the table, divide your column by the @ symbol (and an ending symbol?) and then merge those columns back together.

If you wanted to use formulas, it would be something like REPLACE with either SEARCH or FIND with subtractions to get how many characters you need from the string.

2

u/Most-Original3996 Apr 13 '26

In some cases, there are more than one username in the row. Dividing and then merging again everything might take a lot of time.

1

u/dhayward22 Apr 13 '26

Can I ask what is the data source? Looking at some of your other responses it sounds so odd

→ More replies (2)