Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula for anonymize names

Topic Labels: Formulas
Solved
Jump to Solution
1324 2
cancel
Showing results for 
Search instead for 
Did you mean: 
A_Dan
4 - Data Explorer
4 - Data Explorer

Hi all,
I need a formula for anonymizing names in a column. Instead of just hiding the column I would like to have the name anonymized in a different column like this: Matthias -> XXXXhias In this case the name could still be verified but is not traceable for third parties.

Is this possible and what could a formula look like?

Have a nice Weekend! :slightly_smiling_face:
Dan

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @A_Dan! :grinning_face_with_big_eyes: This is definitely doable. The big question, though, is how to deal with names of varying lengths. In your example, you anonymized the first four characters, but what if someone’s name is Bill, or Al? Do you want the formula to mask a minimum of four characters, or perhaps a certain percentage of characters?

If we go with a four-character minimum, this formula does the trick:

REPT("X", MIN(4, LEN(Name))) & RIGHT(Name, LEN(Name) - 4)

Screen Shot 2020-11-28 at 11.55.18 AM

Change 4 to some other number if you want more or fewer Xs.

If you want a percentage of the name’s length to be anonymous, this would work:

REPT("X", ROUNDDOWN(LEN(Name) * .5, 0)) & RIGHT(Name, LEN(Name) - ROUNDDOWN(LEN(Name) * .5, 0))

That anonymizes half of the name; replace .5 with another value to change the ratio.

Screen Shot 2020-11-28 at 12.03.05 PM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @A_Dan! :grinning_face_with_big_eyes: This is definitely doable. The big question, though, is how to deal with names of varying lengths. In your example, you anonymized the first four characters, but what if someone’s name is Bill, or Al? Do you want the formula to mask a minimum of four characters, or perhaps a certain percentage of characters?

If we go with a four-character minimum, this formula does the trick:

REPT("X", MIN(4, LEN(Name))) & RIGHT(Name, LEN(Name) - 4)

Screen Shot 2020-11-28 at 11.55.18 AM

Change 4 to some other number if you want more or fewer Xs.

If you want a percentage of the name’s length to be anonymous, this would work:

REPT("X", ROUNDDOWN(LEN(Name) * .5, 0)) & RIGHT(Name, LEN(Name) - ROUNDDOWN(LEN(Name) * .5, 0))

That anonymizes half of the name; replace .5 with another value to change the ratio.

Screen Shot 2020-11-28 at 12.03.05 PM

Hello Justin,
thank you for your help and the perfect solution :ok_hand: . I think the percentage solution is smarter to use. I will take this formula.

Best regards

Dan