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

This should do,
Increase 10 if needed.

Please Login or Register  to view this content.

Hi,

you can use the Data Convert assistant or see in VBA help TextToColumns method …

____________________________________________________________________
Je suis Charlie

Many thanks Jindon for the prompt reply and the VBA. I just tested the VBA on the sample sheet and it worked fine. I will test in the big data set later today.

Is your comment to "Increase 10" in case I have more that 10 organizations in the cell?


Thanks

Yes, that is correct.

Dear Fdibbins and Glenn,
Many thanks for the formulas and will test them along the VBA. I will keep you posted.

I will go a head and mark the thread solved.

This forum is great help.