Separate records without any f

Question

Hi ,

I've my input like below

InputLov, Ashley [OPS ACCOUNTING MGR], Klun, Ed  [FIN ACCTNG MANAGER], Cary, Patt LCan, 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

OutputLov Ashley [OPS ACCOUNTING MGR] Klun Ed  [FIN ACCTNG MANAGER]Cary, Patt L Can , Abegail Joy S.Pagatpatan, ChristianOlivia 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, KatherinaTuan, AdaCarpio, Kris jeremy A.

How to do this?

Ben_H · Accepted Answer

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