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?
