reorganising data for mail merge

Reljoy

New Member
Joined
May 6, 2010
Messages
8
I have a spreadsheet as a .xls generated from a swimming carnival program.

It gives me data in column A that looks like:

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

<tbody>
</tbody>

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

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

<tbody>
</tbody>


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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Reljoy

New Member
Joined
May 6, 2010
Messages
8
I have had a friend who did this for me in a text editor using regular expressions.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,313
Messages
5,635,499
Members
416,860
Latest member
coen078

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top