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?
Solved! Go to 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
@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!