Hi Team, good day
Trying to separate data in the same cell, bit hard to use "Text to Column" to do this, so coming to seek your kind help to solve it. Much appreciated.
Data format required to split : "(name1);(ID1),(name2);(ID2),(name3);(ID3),....,(namex);(IDx)" => create multi column to fit individual data in brackets
Input
| Client | Country | Code Name | Primary | Secondary | Undefined |
| ABC | AA | X3AVC3 | Amy A;AMYA | BOB B;BOBB,Cindy C;CINDYC | Mnm M;MM |
| BCD | BB | 123DSA | Amy A;AMYA | | |
| CDE | CC | DFJAASD2 | | Cindy C;CINDYC,Dave D;DAVD,Eason E;EAE | |
| DEF | DD | 1234FGDA | Dave D;DAVD | | |
| EFG | DD | 1DFGBTAS | Frank F;FRANKF,Geo G;GEOG | | Xin X;XINX,Zzz Z;ZZZZ |
Expect Output
| Client | Country | Code Name | Primary1 | Primary1 ID | Primary2 | Primary2 ID | Secondary1 | Secondary1 ID | Secondary2 | Secondary2 ID | Secondary3 | Secondary3 ID | Undefined1 | Undefined1 ID | Undefined2 | Undefined2 ID |
| ABC | AA | X3AVC3 | Amy A | AMYA | | | BOB B | BOBB | Cindy C | CINDYC | | | Mnm M | MM | | |
| BCD | BB | 123DSA | Amy A | AMYA | | | | | | | | | | | | |
| CDE | CC | DFJAASD2 | | | | | Cindy C | CINDYC | Dave D | DAVD | Eason E | EAE | | | | |
| DEF | DD | 1234FGDA | Dave D | DAVD | | | | | | | | | | | | |
| EFG | DD | 1DFGBTAS | Frank F | FRANKF | Geo G | GEOG | | | | | | | Xin X | XINX | Zzz Z | ZZZZ |
