Separate data column into 2

[bigdaddyvette]

Hello Everyone,  Was Hopeing to get some assistance. I have a Column with Last Name, First Name - Employee ID. 

for example:

Colunm

Garcia, John - I123456

Ramirez-Sanchez, Maria - U123456

Turner - Rogan, Roger - F123456

I Currently have a regex to replace "\s-\s" to "|" followed with a text to Column to separate a "\s|\s" which works perfect for first two names. 

But recently rec a new name "Turner - Rogan, Roger - F123456" and instead of giving me a final result of  1 column being just "Last Name, First Name" second column being "Employee ID" im getting "First Last Name(Turner)" in column 1 and "Last last Name, First Name - Employee ID(Rogan,Roger | F123456".

Final result Im attempting to get is "Employee Id" In its own column and Name in Another.

Anything that would work for all three senarios or need to separate with different Actions?

[JosephSerpis · Accepted solution]

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

[AndrewS · Accepted solution]

Hi @bigdaddyvette 

The below syntax in a regex tool set to parse should work:

^(.+)\s-\s(\w{7})$

This will create two output fields and no need to do any cleansing / text to columns etx.

Let me know if there any issues. Cheers.

[bigdaddyvette]

Thks