Split a field into multiple fields based on specific words? (Regex parse/tokenize?)

Question

Hi, I've got a field where most of the values look something like this:

Who : Scott Wernetta_x000D_
What :New Rule to Allow from DEV DMZ (g-mulesoft-dev-dmz-runtime) and TEST DMZ (g-mulesoft-test-dmz-runtime) servers

Why : As per the Request RITM0...

and I would like to split this up into columns of Who, What, and Why. Problem is that they're not always on new lines so I can't use Text To Columns 
, and sometimes there are completely different descriptions like this:

Add Firewall to Panorama USATLPH-EFW3 and USATLPH-EFW4_x000D_
_x000D_
Note: This template can only be used to add a new physical firewall to an existing site using existing rules.

So basically I would like to parse/tokenize this into 4 columns: Who, What, Why, and all else. I tried using Regex but wasn't sure how to do this, can anyone help please? Basically I want the 1st field to return after Who but before What, 2nd after What but before Why, etc. Thanks in advance.

mceleavey · Accepted Answer

Hi @arthurmauk ,

We don't really have enough information here, and I assume you have an identifier to determine which records belong to which, but I've built this to parse and pivot the data that contains WHO and WHAT into columns, then appends the additional text.

If there is an identifier then you would join on that rather than append.

This gives the following:

Workflow attached.

I hope this helps,

M.