Folks,
This is my first time posting
and am really grateful for any help with my puzzle!
I'm working on a project where I have to count author publications across several academic journals. I have downloaded and organized all the data for these journals, but have been struggling to parse things out for an easy count.
I've been searching the forum for a straightforward way to split semi-colon delimited author names for a single article, create one row per co-author name and carry over the article details to the newly created rows.
My data looks something like this (except that rows continue all the way to column BD):
<tbody>
[TD="align: center"] Authors [/TD]
[TD="align: center"] Number of Authors [/TD]
[TD="align: center"] Title [/TD]
[TD="align: center"] Periodical [/TD]
</tbody>
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]The Winner's Curse of Human Capital[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Åstebro,Thomas[/TD]
[TD="align: center"]1[/TD]
</tbody>
[/TD]
[TD="align: center"]Entrepreneurship: Theory & Practice[/TD]
[TD="align: center"]Demircan;Erturk,Alper[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Technological Change[/TD]
[TD="align: center"]Small Business Economics[/TD]
</tbody>
I would like the data to look like this:
<tbody>
[TD="align: center"] Authors [/TD]
[TD="align: center"] Number of Authors [/TD]
[TD="align: center"] Title [/TD]
[TD="align: center"] Periodical [/TD]
</tbody>
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]The Winner's Curse of Human Capital[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Bernhardt,Irwin[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]The Winner's Curse of Human Capital[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Åstebro,Thomas[/TD]
[TD="align: center"]1[/TD]
</tbody>
[/TD]
[TD="align: center"]Entrepreneurship: Theory & Practice[/TD]
[TD="align: center"]Demircan;[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Technological Change[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Erturk,Alper[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Technological Change[/TD]
[TD="align: center"]Small Business Economics[/TD]
</tbody>
In short, the author cells are split into multiple rows and all the adjacent data about the articles they co-authored up to Column BD gets carried over to the newly created row. There are a total of 7180 rows in my worksheet. My version of Excel is 2010.
Thank you advance for your help!
This is my first time posting

I'm working on a project where I have to count author publications across several academic journals. I have downloaded and organized all the data for these journals, but have been struggling to parse things out for an easy count.
I've been searching the forum for a straightforward way to split semi-colon delimited author names for a single article, create one row per co-author name and carry over the article details to the newly created rows.
My data looks something like this (except that rows continue all the way to column BD):
<tbody> [TD="align: center"]Åstebro,Thomas;Bernhardt,Irwin |
<tbody> [TD="align: center"]Basic Statistics on the Success |
<tbody>
[TD="align: center"] Authors [/TD]
[TD="align: center"] Number of Authors [/TD]
[TD="align: center"] Title [/TD]
[TD="align: center"] Periodical [/TD]
</tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]The Winner's Curse of Human Capital[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Åstebro,Thomas[/TD]
[TD="align: center"]1[/TD]
</tbody>
[TD="align: center"]Entrepreneurship: Theory & Practice[/TD]
[TD="align: center"]Demircan;Erturk,Alper[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Technological Change[/TD]
[TD="align: center"]Small Business Economics[/TD]
</tbody>
I would like the data to look like this:
<tbody> [TD="align: center"]Åstebro,Thomas |
<tbody> [TD="align: center"]Basic Statistics on the Success |
<tbody>
[TD="align: center"] Authors [/TD]
[TD="align: center"] Number of Authors [/TD]
[TD="align: center"] Title [/TD]
[TD="align: center"] Periodical [/TD]
</tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]The Winner's Curse of Human Capital[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Bernhardt,Irwin[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]The Winner's Curse of Human Capital[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Åstebro,Thomas[/TD]
[TD="align: center"]1[/TD]
</tbody>
[TD="align: center"]Entrepreneurship: Theory & Practice[/TD]
[TD="align: center"]Demircan;[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Technological Change[/TD]
[TD="align: center"]Small Business Economics[/TD]
[TD="align: center"]Erturk,Alper[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Technological Change[/TD]
[TD="align: center"]Small Business Economics[/TD]
</tbody>
In short, the author cells are split into multiple rows and all the adjacent data about the articles they co-authored up to Column BD gets carried over to the newly created row. There are a total of 7180 rows in my worksheet. My version of Excel is 2010.
Thank you advance for your help!