On Tue, 26 Jan 2010 10:11:01 -0800, 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
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;
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.
John W. Vinson [MVP]