Follow up Question – Convert one field into three fields

[Doug]

I did not know how to convert a one field address in EXCEL to a three field
address in ACCESS and I posted a question. The address in EXCEL was one
field and I needed to convert the one field into three fields in ACCESS. For
example, the EXCEL one field address was: 123 W Maple (one field). I needed
to convert this to three fields in ACCESS. Field 1: 123; Field 2: W; Field
3: Maple. I received a reply to my question and I followed the instructions
and everything worked. Except I have only two fields. Field 1: 123; Field
2: W Maple. Now I have a follow up question.

After I ran the update query, the updated ACCESS address was in two fields.
Field one was 123, and Field 2 was W Maple. Do you know how to get the
street direction into a separate field? For example, the address is shown
as 123 W Maple (all one field). I need to convert this to three fields in
ACCESS. Field 1 is 123; Field 2 is W; and Field 3 is Maple.

I used the method that was recommended and I imported the EXCEL worksheet
into ACCESS. And I created the Update query and it worked. But I do not
know how to get the street direction (N,S,E,W) in a separate ACCESS field.
Do you know how I can create three fields in ACCESS from one field?

This is what I used to create two fields from one field:

Run an Update query updating AddressNumber to

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

and Street to

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

Thank you for your help with this.
Doug

 

[Tom Wickerath]

Hi Doug,

You could always run the same update query, except just change the field
names. For example, if the value "W Maple" is now saved in a field named
"Street", then your update queries would become:

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

and Street to

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

Personally, I find it much easier to split the data in Excel, and then
import it. Open your spreadsheet and select the field that contains the full
address. Then click on Data > Text to columns... Choose the delimited
option, and specify a space on the next screen. You can also place a tick in
the "Treat consecutive delimiters as one" in case some of your data includes
multiple spaces in-between parts of the address.

Tom
_____________________________________________

:

I did not know how to convert a one field address in EXCEL to a three field
address in ACCESS and I posted a question. The address in EXCEL was one
field and I needed to convert the one field into three fields in ACCESS. For
example, the EXCEL one field address was: 123 W Maple (one field). I needed
to convert this to three fields in ACCESS. Field 1: 123; Field 2: W; Field
3: Maple. I received a reply to my question and I followed the instructions
and everything worked. Except I have only two fields. Field 1: 123; Field
2: W Maple. Now I have a follow up question.

After I ran the update query, the updated ACCESS address was in two fields.
Field one was 123, and Field 2 was W Maple. Do you know how to get the
street direction into a separate field? For example, the address is shown
as 123 W Maple (all one field). I need to convert this to three fields in
ACCESS. Field 1 is 123; Field 2 is W; and Field 3 is Maple.

I used the method that was recommended and I imported the EXCEL worksheet
into ACCESS. And I created the Update query and it worked. But I do not
know how to get the street direction (N,S,E,W) in a separate ACCESS field.
Do you know how I can create three fields in ACCESS from one field?

This is what I used to create two fields from one field:

Run an Update query updating AddressNumber to

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

and Street to

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

Thank you for your help with this.
Doug

 

[Tom Wickerath]

PS. Insert at least three new empty columns to the right of the column in
your spreadsheet that you want to split. And, it should go without saying,
but practice only on a copy of your spreadsheet (not the original).

Tom
_____________________________________________

:

Hi Doug,

You could always run the same update query, except just change the field
names. For example, if the value "W Maple" is now saved in a field named
"Street", then your update queries would become:

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

and Street to

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

Personally, I find it much easier to split the data in Excel, and then
import it. Open your spreadsheet and select the field that contains the full
address. Then click on Data > Text to columns... Choose the delimited
option, and specify a space on the next screen. You can also place a tick in
the "Treat consecutive delimiters as one" in case some of your data includes
multiple spaces in-between parts of the address.

Tom
_____________________________________________

:

I did not know how to convert a one field address in EXCEL to a three field
address in ACCESS and I posted a question. The address in EXCEL was one
field and I needed to convert the one field into three fields in ACCESS. For
example, the EXCEL one field address was: 123 W Maple (one field). I needed
to convert this to three fields in ACCESS. Field 1: 123; Field 2: W; Field
3: Maple. I received a reply to my question and I followed the instructions
and everything worked. Except I have only two fields. Field 1: 123; Field
2: W Maple. Now I have a follow up question.

After I ran the update query, the updated ACCESS address was in two fields.
Field one was 123, and Field 2 was W Maple. Do you know how to get the
street direction into a separate field? For example, the address is shown
as 123 W Maple (all one field). I need to convert this to three fields in
ACCESS. Field 1 is 123; Field 2 is W; and Field 3 is Maple.

I used the method that was recommended and I imported the EXCEL worksheet
into ACCESS. And I created the Update query and it worked. But I do not
know how to get the street direction (N,S,E,W) in a separate ACCESS field.
Do you know how I can create three fields in ACCESS from one field?

This is what I used to create two fields from one field:

Run an Update query updating AddressNumber to

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

and Street to

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

Thank you for your help with this.
Doug