Forum Discussion
Data Masking Emails using Formulae
Hi guys,
I am trying to work on my data masking skills and have ran into a problem. I am trying to mask the email addresses of the following data set (this is a fake set of data from an online course btw).
I want to mask the emails such that: email address removed for privacy reasons will then become b******email address removed for privacy reasons .
I want to make sure that the number of Asterisks represents the number of characters displaced, I have spent a while trying different formulae, however I have been able to generate a formula for the first email from E2 into F2, although this doesn't seem to work for the other rows of emails.
Any help is much appreciated :)#
It appears someone gave you a formula that does work but I cleaned up your version and theirs and made both dynamic arrays so they apply to the whole column at once:
I think this is a variation on your original using REPLACE:
=REPLACE(E2:.E99999,2,FIND("@",E2:.E99999)-3,REPT("*",FIND("@",E2:.E99999)-3))and a multi-segmented version:
=LEFT(E2:.E99999,1) & REPT("*",FIND("@",E2:.E99999)-3) & MID(E2:.E99999,FIND("@",E2:.E99999)-1,LEN(E2:.E99999))and you could further refine them using LET. For example:
=LET(n,FIND("@",E2:.E99999)-3, REPLACE(E2:.E99999,2,n,REPT("*",n)))
3 Replies
It appears someone gave you a formula that does work but I cleaned up your version and theirs and made both dynamic arrays so they apply to the whole column at once:
I think this is a variation on your original using REPLACE:
=REPLACE(E2:.E99999,2,FIND("@",E2:.E99999)-3,REPT("*",FIND("@",E2:.E99999)-3))and a multi-segmented version:
=LEFT(E2:.E99999,1) & REPT("*",FIND("@",E2:.E99999)-3) & MID(E2:.E99999,FIND("@",E2:.E99999)-1,LEN(E2:.E99999))and you could further refine them using LET. For example:
=LET(n,FIND("@",E2:.E99999)-3, REPLACE(E2:.E99999,2,n,REPT("*",n)))I think you should take that link down. Those look VERY much like real names and email addresses, a violation of the policy for this forum.
File is from an online resource from Forage, it is not real data. Thanks for the concern.
