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:

School Name	Cluster Name​	Pathway Name​	Program Name​	Course Name​
School 1​	C1;C2;C3;​	P1;P2;P1;​	PG2;PG14;PG19;	CS1,CS3,CS5;CS6,CS9,CS10;CS11,CS12;
School 2​	C1;C2;	P2;P4;	PG5;PG6;	CS8;CS2,CS7;
School 3​	C3;C4;	P5;P1;	PG2;PG1;	CS11;CS14;

<tbody>
</tbody>

I want it to look like this:

School Name	Cluster Name	Pathway Name	Program Name	Course Name
School 1	C1	P1	PG2	CS1
School 1	C1	P1	PG2	CS3
School 1	C1	P1	PG2	CS5
School 1	C2	P2	PG14	CS6
School 1	C2	P2	PG14	CS9
School 1	C2	P2	PG14	CS10
School 1	C3	P1	PG19	CS11
School 1	C3	P1	PG19	CS12
School 2	C1	P2	PG5	CS8
School 2	C2	P4	PG6	CS2
School 2	C2	P4	PG6	CS7
School 3	C3	P5	PG2	CS11
School 3	C4	P1	PG1	CS14

<tbody>
</tbody>

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.