Data Storage

[Doug]

I would like to create two cells (in ACCESS) from one EXCEL cell . For
example, I inherited a 10,000 record EXCEL worksheet that I will convert to
an ACCESS table. The EXCEL worksheet contains about 10,000 lines (each line
is one record) and each line has an address. The address (in EXCEL) is
shown as 123 Maple and is in one cell. I would like to import the EXCEL
address worksheet and create an ACCESS table called "ADDRESS". This table
would contain one line (one record) for each line in the EXCEL worksheet.
But I would like ACCESS to have two cells, one for the number part of the
address and one for the street name part of the address. So, for the EXCEL
that has 123 Maple (all one cell) I would have an ACCESS table and each
record would include two cells. Cell one would contain the number 123 and
cell two would contain the street name, Maple.
Thank you

 

[Graham Mandeno]

Hi Doug

You posted this question back nearly two weeks ago. Did you not see john
Vinson's reply? He said:

On Sat, 19 Mar 2005 14:59:02 -0800, "Doug"

I'd suggest using File... Get External Data... Import to import the
spreadsheet. Then go into table design view and add the AddressNumber
and Street fields. Run an Update query updating AddressNumber to

Left([ADDRESS], InStr([ADDRESS], " ") - 1)

and Street to

Mid([ADDRESS], InStr([ADDRESS], " ") + 1)

Note that some addresses may give odd results: i.e.

312 1/2 Maple St

will put 1/2 Maple St in the Street field; and an address like

PO BOX 123

will put PO in the street number, and BOX 123 in the STREET. Manual
editing may be required!

John W. Vinson[MVP]