Create two cells from one cell

[Doug]

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" (for example) 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". Would you know of a way
to automate this procedure?
Thank you

 

[John Vinson]

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" (for example) 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". Would you know of a way
to automate this procedure?
Thank you

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]