Extract Multiple Text from a String in to New Column and Then Use That Column to Match

[JPSeagull]

Hi. I have encountered something that I cannot find solutions. Attached is an Alteryx file with two text inputs tools. The first text input is a single column of services that is from raw data created from an ancient reporting system. For the most part, I can do a LEFT([Services],FindString([Services], ' - ')) which will get me all of the single items in this column. However, there are instances where the service entry appears more than one time separated by ", ," (comma, space, comma). It can be two, three or more, but the pattern is the same: services are separated by ", ,". I need to figure out a way, after using the LEFT function for the only (or first) service listed to search and extract only the part of the string after the second comma and up to the " -" (space hyphen) until the string is exhausted.

I have tried to find support topics close to my example, but struggled with using REG_EX where it keeps coming up as best way to attack the problem. In the table below, the second column is my desired output which will often include duplicated values of which I do need. 

Once I can extract all the strings from the condition, I then need to look up the priority of that service. I assume Find and Replace tool is the best option? Will this work if the are more than one service with different priorities? Will I get all the priorities or just the first match? Thanks for the input.

Services (Current View)	Services (Desired View)
AUDMER - FIX - 09/19/2008, ,MER - FIX - 09/19/2008,	AUDMER , MER
AEM - FIX - 01/28/2002, ,DYH - ADDITIONAL - Pending, ,INJMSI - ADDITIONAL - 02/18/2002,	AEM , DYH, INJMSI
GSI - EXISTING - 07/22/2000,	GSI
GSI - FIX - 08/03/2000, ,GVI - END - 11/02/2000,	GSI , GVI
INJMSI - FIX - 06/24/2002, ,INJMSI - FIX - Pending, ,INJMSI - END - 06/12/2000,	INJMSI , INJMSI, INJMSI
IPO - END - 11/13/2000, ,PAT - END - Pending,	IPO , PAT
MER - EXISTING - 10/21/2000,	MER

[Qiu · Accepted solution]

@JPSeagull 
I use the comma as delimiter to break the columns in to several rows. then perform a RegEx parse. hope this works

[flying008 · Accepted solution]

Hi, @JPSeagull 

Maybe this is your want output?

RegEx Expression: 

\,{0,1}[A-Z]*(?=\s-\s[A-Z]{1,}\s)

Add the compare view:

[binuacs · Accepted solution]

@JPSeagull Another way of doing this, similar to @Qiu method. @flying008 nicely done with less tools!!

[JPSeagull]

@flying008  - amazing! Especially think the animated demonstration helped towards my final solution! Thank you!

@Qiu Thank you! It really helped to get the final solution as I used the combination of all three inputs.

@binuacs - Thank you for including the second input for the assigning priority. I really appreciate the time.

With the help of you three, I was able to see what I needed to do to separate into additional rows, by Record ID, do the lookup of priority with Find and Replace and regroup them via Summary. My final version is attached. Again - than you all!

[Qiu]

@JPSeagull 

Good to know it works. Thanks.