Separating data in a field

S

Shanice

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
 
R

Rick B

One post per issue please.

Answered in your other post.

Do a search. This is a very common question.
 
B

Brian

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.
 
Top