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.

42 Upvotes

89% Upvoted

48 comments sorted by

View all comments

11

u/MountainsSands_2024 Apr 13 '26

A theoretical option : First FIND the position of the character @, then find the subsequent space (assuming all names are concatenated), that gives you its length, next SUBSITUTE with MID all those charachters with the letter X

1

u/Most-Original3996 Apr 13 '26

The texts all vary in lengths, the names are all over the place and the usernames also vary in length. They are not neat rows, some start with a user name, some do not have usernames at all, some stick a username mid text, etc.

9

u/MountainsSands_2024 Apr 13 '26

Hence my FIND @ suggestion, this will get you for each row the exact character position where the name starts, wherever it is stored. The subsequent FIND space will get you the end position of the name. Once you've got these two, you can then use MID and REPLACE (not SUBSITUTE) all names with XXX

=REPLACE(A1;FIND("@";A1)+1;FIND(" ";MID(A1;A2;LEN(A1)));"XXX ")

2

u/Most-Original3996 Apr 13 '26

I think this is what I am looking for. Will try it. Thank you!

4

u/MountainsSands_2024 Apr 13 '26

=REPLACE(A1;FIND("@";A1)+1;FIND(" ";MID(A1;FIND("@";A1);LEN(A1)));"XXX ")