Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Replace Text after keywords

Highlighted
8 - Asteroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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.

Highlighted
Alteryx Certified Partner

Hey @fiorano

 

I'd go for something like this:

 

f&r.PNG

Highlighted
8 - Asteroid

@CharlieS

 

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

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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.

Highlighted
8 - Asteroid

@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

Highlighted
8 - Asteroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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.

Labels