Using RegEx to extract part of a string based on another field value

[ansonwun]

Hi, I'd like to extract part of a string based on the value of a field value but got stuck with RegEx. My data is like this: the "Name" column lists out all the individual's name, but it may not be unique. The "Position" column lists the name and position of everyone - so it's the same every row.

Name	Position
Doe, J.	Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
Tan, T.	Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
West, P.Y.	Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
Stan, A.	Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ
Doe, J.	Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQ

What I wanted to do is get this result:

Name	Position
Doe, J.	Doe, J., Manager, Operations
Tan, T.	Tan, T., Director
West, P.Y.	West, P.Y., Clerk, Sales; HQ
Stan, A.	Stan, A., Sales, Global
Doe, J.	Doe, J., Intern, HQ

So far I've only used RegEx with some actual values instead of referring to a field combined with RegEx syntax so I'm not quite sure how to approach this. 2 other complications is that Names could be the same but Position can be different (e.g. 1st vs last row are actually 2 different persons), and there will be user input error putting the delimiter at the wrong place (e.g. the 3rd row, "Sales; HQ" instead of "Sales, HQ")

Thanks in advance!

[BenMoss · Accepted solution]

This gets complex primarily because of the ; HQ; error.

I've built a rule that splits the data to rows on the ; delimiter. Then used the multi row formula tool to pull up the data from the following line if there is no comma within it.

See the attached solution.

Ben

[ansonwun]

Thanks @BenMoss 

Inspired by your solution I've replaced every ": " + [Name] pattern with a delimiter other than ";" so that will take care of that unexpected delimiter inside the string and got what I was looking for.