Regex Name and Spouse Name in same field with varying formats

[AshBlue20]

Hi. I have a list of names that need to be parsed out and RegEx is going to be the best tool. Problem is, RegEx has a fairly high barrier to entry and while I'm understanding the basics, I need help to get through some of the complexities for this issue. 

The problem: The database I'm working from has one field for the name of the Entrant. Since an entrant can be two people and is freetext, there are many variations of formats for the two entrants listed. This is further compounded if Entrant1a and Entrant1b have different last names.

How can I parse this data to have the outcome below.

Entrant Name	Expected Result
Falkenstein, Albert & Margaret	Albert & Margaret Falkenstein
Stonestreet, Maurice & Margaret	Maurice & Margaret Stonestreet
Grow, Jennifer & Gregory Hobe	Jennifer Grow & Gregory Hobe
Tomback, Alexandra & Reisman, Benjamin	Alexandra Tomback & Benjamin Reisman
Fatica, Joshua C & Marisa Lowry-Fatica	Joshua C Fatica & Marisa Lowry-Fatica
Brian C O'Neill	Brian C O'Neill
Gerber-Felton, Courtney E & Dodson Felton	Courtney E Gerber-Felton & Dodson Felton
Marcus Evans & Makahla Peterson	Marcus Evans & Makahla Peterson

I found RegEx_Match and RegEx_Replace and am thinking I need to run the field through the formula tool with some if statements to utilize these formulas. Am I on the right track?

I'm also thinking I should start by splitting the field via Text to columns at any instance of '&' to evaluate the names separately and combine them downstream.

[martinding · Accepted solution]

Hi @AshBlue20,

You are definitely on the right track, in fact, I took a very similar approach as the one you described.

Please find attached the example workflow

 

[AshBlue20]

WOW THANKS! I also download the CReW macro pack. I have seen this around the community, but haven't downloaded it yet. Will play with these in the new year. Thanks again!