separating data

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

Guest

I have a client that sends me a mailing list in Excel. I import it into
Access and run a query to extract the info I need for mailing. My problem is
that they combine the city and state into one field. I want to separate it
into two fields. Can I do this?
 
Brenda,

Most likely you can, but you're not telling us how it's coming! Is it
something like:

Athens, GA
Richmond, VA

etc?

If that's the case, and assuming the field is called fldCity, then you can
separate the two by something like:
City: Left([fldCity], Len([fldCity]) - 4)
State: Right([fldCity], 2)

If the format is not consistent, so there may or there may not be a space
between the comma and the state code, then you will need to determine the
comma position by means of InStr(1,[fldCity],",") in order to extract the
city:
City: Left([fldCity], InStr(1,[fldCity],",") - 1)

Does this cover your case, or is it in a different format?

Nikos
 
Back
Top