Replace Text after keywords

[fiorano]

Hi,

For example If I have a list of Keywords that I am checking against:

Keywords

DOB

USER NUMBER

PASSPORT NUMBER

I am doing a find and replace on a Description Field to see if these words exist.  Is it possible to replace the text following a Keyword with "This text has been removed" ?

Many thanks,

Fiorano

[CharlieS]

@fiorano

This is easily achieved using a RegEx formula. In a Formula tool use the following to replace the DOB cases:

REGEX_Replace([Field1],"DOB \<\w+\>","DOB This text has been removed")

The expression "DOB \<\w+\>" is identifying strings in [Field1] where "DOB " is followed by a string of characters. When that case is true, then "DOB " and the first subsequent string of characters will be replaced with "DOB This text has been removed".

Using the above formula on this input:

DOB 19000101 PLUS SOME ADDITIONAL TEXT

This is the result:

DOB This text has been removed PLUS SOME ADDITIONAL TEXT

DOB \<\w+\> isn't the only way this can be achieved, but the expression will depend on the format of the information following the keyword. If the string following the keyword is only numeric characters then "DOB \d+" could be used.

[LordNeilLord]

Hey @fiorano

I'd go for something like this:

[fiorano]

@CharlieS

Hi. is it possible to use a lookup table within the RegEx?

[CharlieS · Accepted solution]

@fiorano

For that solution, I used a combination of @LordNeilLord's Find/Replace solution and my RegEx solution. I used a universal placeholder for the RegEx that is updated using the Find/Replace method.

Solution attached.

[fiorano]

@CharlieS

This is working great - thanks so much.

One thing I am noticing is that in some lines I have multiple keywords eg

DOB 05/11/1987 - USER NUMBER 399779944

Is there a next level that could check for multiple instances of keyword and remove the following string so the above would look like 

DOB this text has been removed - USER NUMBER  this text has been removed

As you can imagine we have a lot of dirty data !

Thanks again,

Fiorano

[fiorano]

Hi @CharlieS

Just one other request using your solution... what would be the correct expression to find the Keyword from the lookup and place two special characters at the start and end of it (rather than replace the next string following the Keyword?

Thanks again for your help.

Fiorano

[CharlieS]

@fiorano

Adding the characters is easy, just add them in the replacement argument. Here's what the formula above would look like:

REGEX_Replace([Field1],"DOB \<\w+\>","%DOB This text has been removed%")

Solving the multiple keyword replacement took the solution in a different direction. To solve this, I built an iterative macro that loops through the list of keywords input and replaces all instances of that word in that iteration. The iterative macro solution is attached.

I'd also flag @LordNeilLord's response as a solution.