Address Locator for Geocoding Issue (picture)

10-25-2012 05:50 AM
New Contributor
I have a very large dataset of property addresses that I subscribe to. The problem is, the way the  data is currently sorted in excel itcannot be geocoded. The problem is, in the database, it stores the address in a single cell.

For instance "123 main street Denton TX 76205", that is a single cell in the column. Is there a way, either through Excel Macro within ArcGIS or some other method, to quickly go through the database and break apart each of the addresses into additional fields for each entry?

Such as:

"Address" 123 Main Street
"City" Denton
"State" TX
"ZIP" 76205

I will need to break it apart in this manner to geocode the data.
0 Kudos
3 Replies
New Contributor
Bump to the top
0 Kudos
Occasional Contributor III
From the screenshot I noticed that some addres parts are divided by more than one space character.
If it's true, you can try to split an address into desired columns in several steps in Excel.
1. Replace "  " (double space) with "|" character
2. Use Text to Columns tool from Data tab with "|" delimiter
3. Then you can join values to one columns if they were splited unnecessarily with command Cell1 & " " & Cell2
4. Then you can split city-state-zip part with Text to Columns with " " (space) delimiter (the problem may appear when city has two words - then you can join city parts just like in point 3.)

Here's how it looks in Excel:
0 Kudos
Esri Regular Contributor

ArcGIS 10.x locators should accept a single field as input for the entire address, for example the locator Street_Addresses_US that ships free in the Data and Maps with ArcGIS works this way.  If you need help finding Esri locator resources then please enquire with your regional office.

0 Kudos