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.
Solved! Go to 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
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!