Remove string in a parenthetic phrase *inside another parenthetic phrase*

[TimK10]

Fairly novice user question:

I am trying to use a join tool to join together two sets of data but I need to clean the data for them to match.

Data A:   001 Dept (John Doe) 

Data B:   001 Dept (John Doe (On Leave)) 

The issue is the double parentheses at the end is messing me up. I need to remove (On Leave) but keep the the (John Doe). 

If I use the filter tool with command: Regex_Replace([Field1]), " \(.*?\)","") then I lose the entire phrase and am left with 001 Dept 

If I tried using RegEx tool and command look for (On Leave) then I end up with 001 Dept (John Doe)) 

I assume the issue with the RegEx tool is because the parentheses is a grouping command.

Is there a way to do this without using the Text to Column tool and making it into a bunch of columns then recombining them?

[JosephSerpis · Accepted solution]

Hi @TimK10 I mocked up a workflow let me know what you think?

[TimK10]

That worked great. 

Ironically, I didn't mention there are some that are 001 (John Doe (Inherited)) as well but the code doesn't drop those, which is a good thing. 

How does that RegEx code know to catch the phrase "(On Leave)" in a string like 0269 (John Doe (On Leave)) and ignore the "(Inherited)"

0006 (Jane Smith (Inherited)) 

[JosephSerpis]

Hi @TimK10 this part of the regex \<\w+\>\s\<\w+\> is looking for two whole words with a space so hence why it only finds "On Leave". \<\w+\> is one Whole word and \s is a space. 

[Pingu]

Regex expressions is something I am trying to learn better so I don't have to solve things the long way with other tools

But how does the \s\(\<\w+\>\s\<\w+\>\)\) expression always search for two whole words separated by a whitespace. \s is the whitespace but isn't w+ one or more words?