This task may be simple or very complex, I need more info. Also a sample of
a couple of line from the spreadsheet.


The problem is with the number of line in the address. I can't tell from
you posting how to handle the optional address2 such as "suite 231". Is
there a blank column for the optional address2. Is every entry two rows and
we are just taking the second row and putting into the first empty column on
the first row? Are we skipping columns for any special casses if data is
missing?


"Svenman" wrote:

Hello!

I have an unusual looking Excel spreadsheet that is produced by our IT
department. I am looking for some help as how to modify the layout in
anticipation to an export to Access.

The layout of the Excel spreadsheet is listed below. I am using the
pipe character to indicate that the data is in the next cell.
However, the address information (lines 2,3,4) is not separated across
different cells other than by separate rows. Also note that there is
a string of dashes serving as a sort of record delimiter:


Customer Number(n) | Salesman | Date| LOB | Region
William Brown|
123 Main Street|
Rockledge FL 32955|

------------------------------------------------------------------------------------------------------------
Customer Number(n+1) | Salesman | Date| LOB | Region
Ernie Simon|
541 W. Main Street|
Suite 231|
Cocoa Beach FL 32912|
------------------------------------------------------------------------------------------------------------
Customer Number(n+2) | Salesman | Date| LOB | Region
Dave Johnson|
552 Harbor Drive|
Port Richey FL 32119-9818|

I would like to restructure the data so that each "line" holds a
complete record, and do away with the dashed line record delimiter.
Basically, this is what I would like to create:

CustNumber(n) | Salesman | Date| LOB | Region | CustName | Address1 |
Address2 | CityStateZip
CustNumber(n+1) | Salesman | Date| LOB | Region | CustName | Address1
| Address2 | CityStateZip
CustNumber(n+2) | Salesman | Date| LOB | Region | CustName | Address1
| Address2 | CityStateZip

Can anyone suggest some quick code to make this happen?

Thanks in advance,

Sven Asnien