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

Question

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.

NamePositionDoe, J.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQTan, T.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQWest, P.Y.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQStan, A.Doe, J., Manager, Operations; Tan, T., Director; West, P.Y., Clerk, Sales; HQ; Stan, A., Sales, Global; Doe, J., Intern, HQDoe, 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:

NamePositionDoe, J.Doe, J., Manager, OperationsTan, T.Tan, T., DirectorWest, P.Y.West, P.Y., Clerk, Sales; HQStan, A.Stan, A., Sales, GlobalDoe, 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 Answer

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