RegEx - Break up a cell into multiple rows

[SylviaK]

hi, 

I've been trying for quite some time to get from text to result as shown below but have failed. As you can see the 'pattern' is to include everything before 'Applies to:' and the bit that comes straight after Applies to und the next dot. The end result might be 3 rows (as shown in below case) but could also be 1 or n. Can someone pls help? Many thanks in advance. 

Text	Result
Capitol riots: 'Wild' Trump tweet incited attack, says inquiry. A Trump tweet mobilised far-right extremists to converge on Washington DC on the day of last year's Capitol riot, a congressional inquiry has heard. XYZ action: The select committee has been conducting a nearly year-long investigation into the attack on the Capitol.Tuesday's hearing, the seventh since June, focused on a tweet Mr Trump sent in the early hours of 19 December 2020, and a stormy six-hour meeting at the White House that preceded the post. Applies to: Me, You, everyone. Uefa Liverpool final String of errors in French handling, says report. A French inquiry into the security fiasco at the Uefa Champions League final in Paris in May has found that it was caused by a catalogue of administrative errors and failings. XYZ action: The French government initially blamed Liverpool fans and fake tickets for the crowd chaos that led to supporters being tear-gassed and robbed.But a Senate report has found authorities blamed them unfairly. Applies to: everyone. Emmys 2022: Succession leads US TV award nominations. Succession leads the charge for this year's Emmy Awards with 25 nominations, including outstanding drama series and lead actor for Scotsman Brian Cox's portrayal of media mogul Logan Roy.XYZ action: Cox will go up against his co-star and on-screen son, US actor Jeremy Strong. Applies to:me, you.	Capitol riots: 'Wild' Trump tweet incited attack, says inquiry. A Trump tweet mobilised far-right extremists to converge on Washington DC on the day of last year's Capitol riot, a congressional inquiry has heard. XYZ action: The select committee has been conducting a nearly year-long investigation into the attack on the Capitol.Tuesday's hearing, the seventh since June, focused on a tweet Mr Trump sent in the early hours of 19 December 2020, and a stormy six-hour meeting at the White House that preceded the post. Applies to: Me, You, everyone.
Uefa Liverpool final String of errors in French handling, says report. A French inquiry into the security fiasco at the Uefa Champions League final in Paris in May has found that it was caused by a catalogue of administrative errors and failings. XYZ action: The French government initially blamed Liverpool fans and fake tickets for the crowd chaos that led to supporters being tear-gassed and robbed.But a Senate report has found authorities blamed them unfairly. Applies to: everyone.
Emmys 2022: Succession leads US TV award nominations. Succession leads the charge for this year's Emmy Awards with 25 nominations, including outstanding drama series and lead actor for Scotsman Brian Cox's portrayal of media mogul Logan Roy.XYZ action: Cox will go up against his co-star and on-screen son, US actor Jeremy Strong. Applies to:me, you.

[DataNath · Accepted solution]

How does this look @SylviaK? Works for your example but let me know if you have any issues when applying it to a wider data set:

[MarqueeCrew · Accepted solution]

@SylviaK ,

You'll want to gather the following ingredients:

• RegEx Tool
• Formula Tool

The RegEx will use the following expression:

.*?\sApplies to:.*?\.

This means:  everything including a space followed by "Applies to:" followed by anything up to and including a period.

This becomes a TOKEN.  You'll set the output method to Tokenize and Split to Rows.

Now you've got some nasty trailing spaces that you can Trim([Text]) with.

Cheers,

Mark

If I'm wrong about this, then @Aguisande can hold this over my head.

P.S.  No workflows were attached to this post deliberately.

[MarqueeCrew]

@DataNath ,

Looks like we're either right or wrong together.

Cheers,

Mark

[Aguisande]

I think you're both right!

I need to try it, but in theory, both expressions should work.

Great Job!

[SylviaK]

@DataNath & @MarqueeCrew : Many thanks! Both solutions work.