Multi-word string from one field to two fields

[tapasaddict]

I have a column with city and state in one field (separated by a space and/or
a comma). I want to separate the information into two fields (similar to what
you can do in excel using the "Text to Columns" function). How can I do this
using queries?

 

[Jerry Whittle]

(separated by a space and/or a comma).

Here's the sticking point. If every field has a comma, you could use
combinations of the InStr, Left, and Mid functions to find the commas then
split up the field there. Therefore you need to make sure that every record
has a comma.

How about the space? Two words: St. Louis. Two more: San Diego.

Depending on the space could give you really bad results.

 

[John W. Vinson]

I have a column with city and state in one field (separated by a space and/or
a comma). I want to separate the information into two fields (similar to what
you can do in excel using the "Text to Columns" function). How can I do this
using queries?

Des Moines Iowa
Grand Forks Minnesota
Salt Lake City Utah
West Fargo North Dakota


In other words... insufficient information exists in the text string for any
algorithm to do this unambiguously. You'll need either a table of states (with
all the possibilities the data might contain, such as MN, Minn, ND, NDak,
etc.) or a USB (Using Someone's Brain) interface.

You can get a first pass by filtering out the names with a comma:

City: Left([CityAndState], InStr([CityAndState], ",") - 1)
State: Mid([CityAndState], InStr([CityAndState], ",") + 1)

using a criterion of LIKE "*,*" to select only records containing a comma;
and

City: Left([CityAndState], InStrRev([CityAndState], " ") - 1)
State: Mid([CityAndState], InStrRev([CityAndState], " ") + 1)

with a criterion of

NOT LIKE "*,*"

The latter set, at least, will need manual processing for "West Fargo North"
in the state of "Dakota", every city in West Virginia, and so on.