Separate records without any f

[Pandey19]

Hi ,

I've my input like below

Input

Lov, Ashley [OPS ACCOUNTING MGR], Klun, Ed  [FIN ACCTNG MANAGER], Cary, Patt L

Can, Abegail Joy S., Pagatpatan, Christian, Olivia O., Diman, Jonell C.

Canete, Abegail Joy S., Trasmonte jr, Henry D., Dimaculangan, Jonell C.

Peters, Jeff - WFF Home Office [FIN ACCTNG SR CONS]

Yeo, Katherina, Tuan, Ada, Carpio, Kris jeremy A.

But i want this data to be in proper format like below

Output
Lov Ashley [OPS ACCOUNTING MGR]	Klun Ed  [FIN ACCTNG MANAGER]	Cary, Patt L
Can , Abegail Joy S.	Pagatpatan, Christian	Olivia O.	Diman, Jonell C.
Canete, Abegail Joy S.	Trasmonte jr, Henry D.	Dimaculangan, Jonell C.
Peters, Jeff - WFF Home Office [FIN ACCTNG SR CONS]
Yeo, Katherina	Tuan, Ada	Carpio, Kris jeremy A.

How to do this?

[Ben_H · Accepted solution]

Hi @Pandey19,

I'm sure there will be a simple regex way of doing this but I didn't fancy figuring that out on a Friday afternoon!

Here's one way you can do it -

It assumes that the names in your input are always a pair separated by a comma.

Basically it splits the input to rows on the comma, and then groups them up in pairs. I then cross tab back to the format you want.

If you have names with additional embedded commas this will break.

Regards,

Ben

[JBLove]

@Pandey19  - 

@Ben_H  beat me to the solution.  I was attempting this via reg ex, but a simpler approach rather than using the regex might be to just identify the three patterns you have for names (assuming no individual field value has more than one type):

Last Name, First Name [Job Title]

Last Name, First Name MI.

Last Name, First Name

From there you could use a formula tool that uses a condition statement to assess which pattern your field value aligns to and in the "Then" section you could use a replace formula or concatenation formula to insert a delimiter where necessary.

A text to columns tool could then be utilized to separate the text string based on the delimiter utilized.

Good luck!