Extracting and reorganizing values in cells

VBA code in the attached Excel 2003 file translates the zip prefix report in Sheet1,
from vertical to horizontal display of records and writes the rearranged text in Sheet2.
This code would be useful if you had to do this chore more than once. Otherwise,
it just illustrates that any Excel report that has a predictable order to it, can be parsed
and put in some other orderly format.

The original list displays each record’s fields in a column, using 2 or 3 rows per record,
and records are displayed across 10 columns. In each column, one record follows another
without skipping rows, except for dual blank rows that appear to be page-separators.

Formulas could be used but there would be some manual clean-up steps in getting
the results in finished format. VBA code does it all in a second or two.
The code parses the zip prefix from the state abbreviation and stores each in a separate cell.

Steps the code goes through:

- loop through all rows and record in an array, the starting row of each set of 10 records.
Since we now have this information, besides having a reference for starting rows,
we don’t have to worry about the blank page-separator rows in Sheet1, because of the
fact that the 2 or 3 rows following the starting row have data.

- new outer loop: using the record starting row # as a reference, determine if the
current set of 10 records uses 2 rows per record or 3.

- inner loop: capture either 2 or 3 cell values for each individual record and
write results in Sheet2, repeating this for all 10 columns.

- repeat the above 2 steps until all rows in the array of record row-starting numbers have been processed.