topic Re: Another Regex Replace question in Alteryx Designer Desktop Discussions

Another Regex Replace question

AndrewW — Thu, 13 Jun 2024 22:12:29 GMT

I have a regex question. I have many strings, some of which contain a varying number of <> tags embedded in the text. One example is (italics added by me):

During the last portoflio review we. The term loan we own for (TLB-6 USD 2 purchased at

I'd like all of the <> including the text between removing. That would leave the above text as:

During the last portoflio review we. The term loan we own for (TLB-6 USD 2 purchased at

Some of the strings have no <> tags, others have quite a lot. Any solution would need to be able to manage this inconsistency in this free text field.

Thanks!

Re: Another Regex Replace question

MarqueeCrew — Wed, 13 Mar 2019 10:47:16 GMT

In your example, all of the good data comes after the tags. Did you know that you can handle this with string functions?

One way is to use reversestring() and replacefirst() as reversestring(replacefirst(reversestring([field]),">",'|'))

Then you could use a text to columns and parse the data.

Instead of replacefirst, you could use findstring to find the first (last) > sign and then use that value to take the right() most values.

Regex_replace([field],".*>(.*)",'$1')

Cheers,

Mark

Re: Another Regex Replace question

estherb47 — Wed, 13 Mar 2019 11:39:18 GMT

Hi @AndrewW

A formula tool with a simple REGEX_Replace seems to be working here. Try it out on your data and let me know if it works for you.

REGEX_Replace([Field1], "<.*?>", "")

This tells Alteryx to remove everything contained in the <>, no matter how many there are. The ? keeps the text to be removed bound within the <>

If it doesn't work, then I'd recommend adding a Record ID tool to mark each row with an identifier. Then a Regex Parse, tokenizing into rows, using this as the expression: (^|.*?)(?:<.?>). Follow it with a Summarize tool, where you group by the record ID and concatenate the text back together with no delimiter

Cheers!

Esther

Re: Another Regex Replace question

AndrewW — Wed, 13 Mar 2019 14:03:33 GMT

Thanks @estherb47 , I hoped there would be a simple regex solution for this :-)

Re: Another Regex Replace question

AndrewW — Wed, 13 Mar 2019 14:05:18 GMT

Thanks for the suggestion Mark, sadly doesn't work for all scenarios, but appreciate the response