Parsing Irregular but well organized data

Hi All,

I have “irregular” but "well-organized” text in column “A” of the attached file. In each Cell of column “A”, includes a number of authors and their affiliated organizations participating in research projects. The authors and grouped by their organization along with the departments and full address and country.

The data is organized in the following way

In each cell of Column “A”, Names of authors are included within brackets “[ ]” and each Name is separated by a Semicolon “;”. In all cases, the full details of the affiliation including address and country are listed after the closing of the bracket "]".

I would like, if possible a VBA, or other solution, that can parse the data on column “A” into the needed columns starting on Column “B”. Each column should contain the authors of the participating organization, i.e., parsing based on organization. Please note that some cells contains one organization as in Row 6 of the attached. In this case, just copy to B.

The attached file contains partial list of the data (thousands of rows) and I think the attached is self explanatory. I manually parsed rows 1 to 6. The rest of the data to test the solution


Many thanks in advance. Taisir