reorganising data for mail merge

[Reljoy]

I have a spreadsheet as a .xls generated from a swimming carnival program.

It gives me data in column A that looks like:

[TABLE="width: 416"]
<tbody>[TR]
[TD]Appleby, Esmae - Female - Year: 7 - Comp#: - T - DOB: 14/03/2003[/TD]
[/TR]
[TR]
[TD]#73 Girls 12 100 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Bonnyville Sarah - Female - Year: 7 - Comp#: - T - DOB: 11/03/2003[/TD]
[/TR]
[TR]
[TD]#13 Girls 12 50 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#35 Girls 12 50 Fly Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#73 Girls 12 100 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Capper Allison - Female - Year: 7 - Comp#: - C - DOB: 16/01/2003[/TD]
[/TR]
[TR]
[TD]#13 Girls 12 50 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#57 Girls 12 50 Breast Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Floss Chantelle - Female - Year: 7 - Comp#: - C - DOB: 20/01/2003[/TD]
[/TR]
[TR]
[TD]#13 Girls 12 50 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#57 Girls 12 50 Breast Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#73 Girls 12 100 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#87 Girls 12 50 Back Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Frost, Jack - Male - Year: 7 - Comp#: 285 - C - DOB: 29/01/2003[/TD]
[/TR]
[TR]
[TD]#4 Boys 12-14 200 IM Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#21 Boys 12 50 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#43 Boys 12 50 Fly Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#54 Boys 12-14 200 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#65 Boys 12 50 Breast Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#80 Boys 12 100 Free Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]#95 Boys 12 50 Back Secondary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want it to look like: (there could be up to 8 events for each student):

[TABLE="width: 1849"]
<tbody>[TR]
[TD]Appleby, Esmae - Female - Year: 7 - Comp#: - T - DOB: 14/03/2003[/TD]
[TD]#73 Girls 12 100 Free Secondary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bonnyville Sarah - Female - Year: 7 - Comp#: - T - DOB: 11/03/2003[/TD]
[TD]#13 Girls 12 50 Free Secondary[/TD]
[TD]#35 Girls 12 50 Fly Secondary[/TD]
[TD]#73 Girls 12 100 Free Secondary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Capper Allison - Female - Year: 7 - Comp#: - C - DOB: 16/01/2003[/TD]
[TD]#13 Girls 12 50 Free Secondary[/TD]
[TD="colspan: 2"]#57 Girls 12 50 Breast Secondary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Floss Chantelle - Female - Year: 7 - Comp#: - C - DOB: 20/01/2003[/TD]
[TD]#13 Girls 12 50 Free Secondary[/TD]
[TD]#57 Girls 12 50 Breast Secondary[/TD]
[TD]#73 Girls 12 100 Free Secondary[/TD]
[TD]#87 Girls 12 50 Back Secondary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frost, Jack - Male - Year: 7 - Comp#: 285 - C - DOB: 29/01/2003[/TD]
[TD]#4 Boys 12-14 200 IM Secondary[/TD]
[TD]#21 Boys 12 50 Free Secondary[/TD]
[TD]#43 Boys 12 50 Fly Secondary[/TD]
[TD]#54 Boys 12-14 200 Free Secondary[/TD]
[TD]#65 Boys 12 50 Breast Secondary[/TD]
[TD]#80 Boys 12 100 Free Secondary[/TD]
[TD]#95 Boys 12 50 Back Secondary[/TD]
[/TR]
</tbody>[/TABLE]


Then the data can be mail merged to a parent letter.
How can I make Excel do this?
I want it to be able to be reused year after year.

 

[Reljoy]

I have had a friend who did this for me in a text editor using regular expressions.