How to split string values using delimiter into multiple rows in Excel 2010

[FirAzad]

Hi,

I need help in splitting string values for multiple columns into multiple rows:

Here's how my data is currently stored:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]School Name
[/TD]
[TD]

Cluster Name​

[/TD]
[TD]

Pathway Name​

[/TD]
[TD]

Program Name​

[/TD]
[TD]

Course Name​

[/TD]
[/TR]
[TR]
[TD]

School 1​

[/TD]
[TD]

C1;C2;C3;​

[/TD]
[TD]

P1;P2;P1;​

[/TD]
[TD="align: right"]PG2;PG14;PG19;[/TD]
[TD]CS1,CS3,CS5;CS6,CS9,CS10;CS11,CS12;[/TD]
[/TR]
[TR]
[TD]

School 2​

[/TD]
[TD="align: right"]C1;C2;[/TD]
[TD="align: right"]P2;P4;[/TD]
[TD="align: right"]PG5;PG6;[/TD]
[TD]CS8;CS2,CS7;[/TD]
[/TR]
[TR]
[TD]

School 3​

[/TD]
[TD="align: right"]C3;C4;[/TD]
[TD="align: right"]P5;P1;[/TD]
[TD="align: right"]PG2;PG1;[/TD]
[TD]CS11;CS14;[/TD]
[/TR]
</tbody>[/TABLE]

I want it to look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]School Name[/TD]
[TD]Cluster Name[/TD]
[TD]Pathway Name[/TD]
[TD]Program Name[/TD]
[TD]Course Name[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C1[/TD]
[TD]P1[/TD]
[TD]PG2[/TD]
[TD]CS1[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C1[/TD]
[TD]P1[/TD]
[TD]PG2[/TD]
[TD]CS3[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C1[/TD]
[TD]P1[/TD]
[TD]PG2[/TD]
[TD]CS5[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C2[/TD]
[TD]P2[/TD]
[TD]PG14[/TD]
[TD]CS6[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C2 [/TD]
[TD]P2[/TD]
[TD]PG14[/TD]
[TD]CS9[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C2 [/TD]
[TD]P2[/TD]
[TD]PG14[/TD]
[TD]CS10[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C3[/TD]
[TD]P1[/TD]
[TD]PG19[/TD]
[TD]CS11[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]C3[/TD]
[TD]P1[/TD]
[TD]PG19[/TD]
[TD]CS12[/TD]
[/TR]
[TR]
[TD]School 2[/TD]
[TD]C1[/TD]
[TD]P2[/TD]
[TD]PG5[/TD]
[TD]CS8[/TD]
[/TR]
[TR]
[TD]School 2[/TD]
[TD]C2[/TD]
[TD]P4[/TD]
[TD]PG6[/TD]
[TD]CS2[/TD]
[/TR]
[TR]
[TD]School 2[/TD]
[TD]C2[/TD]
[TD]P4[/TD]
[TD]PG6[/TD]
[TD]CS7[/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]C3[/TD]
[TD]P5[/TD]
[TD]PG2[/TD]
[TD]CS11[/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]C4[/TD]
[TD]P1[/TD]
[TD]PG1[/TD]
[TD]CS14[/TD]
[/TR]
</tbody>[/TABLE]

I have separated the courses within same cluster/pathway/program/ with commas delimiter. I have more than 10 schools and each have multiple cluster/pathway/program/courses in it.

First I need to split values between semicolon ( and then split course values between commas (,).

I can use macros. Any help would be really appreciated!!!

Thanks,
Firthouse

 

[ghrain22]

Text to columns in the data tab.

Edit:
Sorry I misread. Although you could still do text to columns and throw it all in a pivot table(with tablular layout and repeating items) afterward. The only other way I can think of would involve a macro.