Good morning community,
I am having the hardest time writing an expression in RegEx to split a text string into three separate columns. The default format on the text string is Employee: LastName, FirstName (123456)
I'd like to drop the "Employee: " piece of the string and parse the remaining into 3 columns- Last Name, First Name & Employee ID.
I was able to parse out the employee ID into a separate field by escaping out the parentheses but I can't seem come up with a complete expression that will accomplish all three steps at once.
Any help would be greatly appreciated.
Solved! Go to Solution.
You can manage this through the regex tool, but I'm going to show you how to do it in a formula. Let's define 3 new string variables (Last_Name, First_Name and Employee_ID) and here are the formulas to try:
Regex_Replace([Text_String],".*?\s(.*?),.*",'$1')
Regex_Replace([Text_String],".*?,(.*?)\s\(.*",'$1')
Regex_Replace([Text_String],".*?\((.*)\)",'$1')
The first formula looks for anything up to the first SPACE and then grabs everything until the first COMMA. That is GROUP #1. It then puts GROUP 1 as the output.
The second formula looks for anything up to the first COMMA followed by a SPACE and then grabs everything until the first SPACE followed by an OPEN PARENS. That is GROUP #1 for the second formula output.
The third formula looks for anything up to the first OPEN PARENS and then grabs everything up to the last CLOSE PARENS.
Cheers,
Mark
This is fairly simple.
I would actually put your regex tool in 'Parse' mode. You then specify your new columns with brackets.
Your regex statement would then look something like.
Employee: ([[:alpha:]]+), ([[:alpha:]]+) \((\d+)\)
In the output settings in the lower part of the configuration panel you can then name each of your 'groups' or new columns accordingly.
Ben