Multiple rows from a single record?

[artistdedigital]

Hi all excel gurus,

I have a large dataset where I have a column of authors where the authors are split based on a semicolon; Now I need to split all these authors into an individual rows of record along with all the other data relative to that record should duplicate.

Is there some way I can make excel to look for the semicolon ; and split it to a separate row with all the other data kept intact?

Can someone please help me how can I achieve that? Attached a sample spreadsheet for your reference.

Thanks in advance.

Attachment 358008

Attachment 358010

[samba_ravi]

see the attached file;
One supporting column was created

[artistdedigital]

Hi Siva,

Thanks heaps for your help. I am sorry for asking this but can you please explain what the "Supporting Column" is doing and what those numbers represent? It solution looks excellent and I need to implement this to my actual worksheet exactly the way you did. Thanks again mate.

[TMS]

He has added an "index". The first entry is 1. He then counts the semi-colons ( in the name cell and adds 1. That is then added to the index to set the value of the index for the next row ... and so on. The last index value, against the empty row, indicates when to stop.

The formulae then use those indexes to determine how many times to repeat the row, extracting the names as required.

Neat.

[artistdedigital]

He has added an "index". The first entry is 1. He then counts the semi-colons ( in the name cell and adds 1. That is then added to the index to set the value of the index for the next row ... and so on. The last index value, against the empty row, indicates when to stop.

The formulae then use those indexes to determine how many times to repeat the row, extracting the names as required.

Neat.

Thanks mate...this is indeed neat...

[TMS]

You're welcome. Thanks for the rep.