Can I do reverse concantination in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
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.
 
Try this

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