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
Solved! Go to Solution.
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.
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.
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
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
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.