convert column to row

[Guest]

I have a list of several hundred staff in a column - see below
(A1) title firstname last name
(A2)address 1
(A3) address2
(A4) city state postcode
(a5) Phone
(A6) skill1
(a7) skill2
(a8) skill3 [number of skills varies between 1-8]
(a9)blank row
(a10) title firstname last name of person 2
how would I convert this to a row for each person - (splitting out the name
field into three cells and transposing all other cells - then starting in the
next row with the next person - see below
(A1) title (B1) firstname (c1) lastname (d1) add1 etc ... [skills n=1-8]
(A2) title (b2) firstname ...
I have about 500 entries to convert - so if you could help it would be very
very much appreciated

 

[Guest]

actually looking at this (a4) needs to be split into three columns as well as
(a1)

 

[Bob I]

From what I can see the switch over is compounded by the "variable
number of skill entries". This means that you would need to use VB to
repeat the "skills" line until you see a blank line.

actually looking at this (a4) needs to be split into three columns as well as
(a1)

:

I have a list of several hundred staff in a column - see below
(A1) title firstname last name
(A2)address 1
(A3) address2
(A4) city state postcode
(a5) Phone
(A6) skill1
(a7) skill2
(a8) skill3 [number of skills varies between 1-8]
(a9)blank row
(a10) title firstname last name of person 2
how would I convert this to a row for each person - (splitting out the name
field into three cells and transposing all other cells - then starting in the
next row with the next person - see below
(A1) title (B1) firstname (c1) lastname (d1) add1 etc ... [skills n=1-8]
(A2) title (b2) firstname ...
I have about 500 entries to convert - so if you could help it would be very
very much appreciated