I need help with VB Script or Python. I have a "CityStateZip" field and I am trying to break it down into a "City" field, "State" field and a "Zip" field. The value in CityStateZip looks like "San Francisco, CA 94110" What scripts can I use in Field Calculate to separate out these values?
To get city (assuming no city names contain a comma):
!address!.split(",")[0]
To get state:
!address!.split(",")[1].split(" ")[1]
To get zip:
!address!.split(",")[1].split(" ")[2]
There may be some special cases where this doesn't work, depending on your data. This works for data like: "CityName"[comma][space]"State"[space]"Zip"
Much thanks Darren!
Hi Chuck,
How familiar are you with python? There are some fairly easy ways to do this, you will need to make 3 new fields for City, State, and Zip. In the field calculator, select python
For the city field try
!CityStateZip!.split(",")[0]
For the State field try
!CityStateZip!.split(",")[1][1:3]
for the Zip field try
!CityStateZip!.split(",")[1][4:]
You definitely will need to spot check your results, I'm basing this entirely on your entries all being similar to the one posted, without it getting very fancy.
If you have the luxury of being sure the values are comma separated...
If not you use an AGOL endpoint and build URLs with your data:
Or geocode the data with the USA Postal locator used by AGOL:
\\dharma\Benchmark_Data\Geocoding\LocatorImprovements\Locators_Release_July2014\LocatorData\USA