Simpler Find/Replace String in Field or Field Name?

In an attempt to anonymize my data, I'm using a multi-field formula with REGEX to replace customer names in the data, and a dynamic rename with REGEX to replace customer names in field headers, like this:

Multi-Field:

findstring([_CurrentField_], “Cust1”)!=-1 then

REGEX_Replace([_CurrentField_], “Cust1”, "John")

elseif findstring([_CurrentField_], “Cust1”)!=-1 then

REGEX_Replace([_CurrentField_], “Cust1”, "Paul")

else [_CurrentField_] endif

Dynamic Rename:

IF
findstring([_CurrentField_], "Cust1")!=-1 then
REGEX_Replace([_CurrentField_], "Cust1", "John")
elseif findstring([_CurrentField_], "Cust2")!=-1 then
REGEX_Replace([_CurrentField_], "Cust2", "Paul")
else [_CurrentField_] endif

The thing is, I can get away with this because I'm only replacing a small number of strings. But what if there were many strings to replace?

Is there a simpler way to accomplish this than 2 full paragraphs of REGEX code?

Regex

Tips and Tricks

Best Practices

Join

Preparation

Accepted answers

jdunkerley79

Did a simple thing for masking names in data. If this is what you mean will do one for fields

This will create a unique ID for each name and replace the sepcified fields with Custxxx for it.

Can also be done on Field Names as well. Attached a second sample showing masking fieldnames. Masked ID are consistent on both data sets

mask Data Names.yxmd

mask Data Names and Field Name.yxmd

jdunkerley79

Try the attached macro.

This can be used to mask columns selectively. It takes in a data set and masked table set. Any record not found get added to masked set and then the resulting data set and unioned masked data is returned.

Field name masking is another process but did it by taking output of Cust mask.

Let me know what you think.

Mask Tool Demo.yxzp

MaskTool.yxmc

All comments

jdunkerley79

I would try something like Transpose to make it one field for all that I need to replace

Then use a find and replace tool to replace the entries

FInally use a Cross Tab tool to put back to original layout.

You might want to use a record ID to make the join back easier.

Just a quick idea but think would work

cbridges

I'm not sure if making it one field would make too much of a difference... basically it's already one field because I'm using the multi-field tool, so it's always "current field". I was mostly wondering if there's a less verbose solution, either using a lookup table of some kind or if there's some way to word the formulas differently. This was my 4th or 5th attempt, because I soon found out that a simple Replace(String, Target, Replacement) didn't work.

What would be super cool is if some wizard out there made an anonymizer macro, where you could quickly group the fields you need to change and then it would add a column for replacement value (maybe even automatically generated like Cust001, Cust002 etc.), then create a way to replace one value with the other throughout. Hmm... sounds like a good side project for me to try.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703