Can I do reverse concantination in Access?

[Guest]

I know how to concantinate a string of text, but can I do the reverse in
Access? I have a full address field in a table. The field is titled
"Customer Address" and looks something like this: 123 First Street, Simi
Valley, CA 93065 I would like to break this address up into three different
fields. The first field I would like to name "Street Address" and include
the 123 First Street. The second field would be titled "City" and I would
like it to include Simi Valley. The third field would be "State" and the
fourth field would be "Zip Code". Can I do this?

 

[Guest]

If the Street, city and state always seperated by "," then you can use the
split function to get the values.

=Split(TextField,",")(0) ' Street
=Split(TextField,",")(1) 'City
=Split(TextField,",")(2) 'State

The text field also as to contain all three of the parts, and if somewhere
youll have more then three "," it will mess up the data.

 

[Michael S. Montoya]

With the item that received the State info, the Split would have returned
the State and Zip. What you could do is look for a space charachter (
Instr(strState," ") ) in the text that was returned and use the Left$ and
Right$ using the location the space was found to split up the state and zip.

 

[Guest]

Try this

=Split(TextField,",")(0) ' Street
=Split(TextField,",")(1) 'City
=Split(Split(TextField,",")(2)," ")(0) ' State
=Split(Split(TextField,",")(2)," ")(1) ' Zip code