Separating data in a field

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

Guest

I have an excel file that has city, state and zip in one field. Is it
possible to separate them in Access, and if so, how?

Thank you
 
One post per issue please.

Answered in your other post.

Do a search. This is a very common question.
 
Are they separated by any common character (i.e. a space or comma or both)?
If so, you can just copy the entire field to the City, State, and ZIP fields,
then do search-and-replace (either in Excel before importing or in Access
after importing).

Since there are three sections, you may need to search/replace ",*" with
nothing in the City, replace "*, " with nothing in the ZIP, and then make the
State equal to the middle portion of the original field - starting at the
point that is the length of City +2 (to account for the comma and space) with
a total length of the original length less the length of ZIP + 2 (again, to
account for the comma and space)

How's that for a complex statement. Maybe someone has an easier way.
 
Back
Top