topic Re: Standardize addresses tool in Data Management Questions

Standardize addresses tool

KennethEggering — Sun, 17 Nov 2013 21:02:56 GMT

Hi,

I'm just wondering how I can parse an apartment/building number out of an address. I have an excel sheet that has 1100+ addresses, and about half of them are in the "123 Hwy 24, #456" format. In this format they are not able to be standardized. I've tried changing the # sign to Apt, Bldg, and various other things. Changing the format to "123 Apt 456 Hwy 123" doesn't work either, and I'm not sure how I would make that change to all 500+ fields (i.e. find and replace wouldn't work). This is the first post I've created. I hope I'm following protocol/etiquette, apologies if I've forgotten something.

[ATTACH=CONFIG]29159[/ATTACH]

Re: Standardize addresses tool

BrianOevermann — Wed, 27 Nov 2013 21:35:20 GMT

You may want to check out the thread: http://forums.arcgis.com/threads/26362-Locator-Style-for-Apt-or-Units

It sounds like that is what you are looking for. There are two "versions" of such a locator style. One has a field added to deal with fractional addresses.

EDIT: Taking another look at the included screen shot you provided, the above-mentioned locator style likely will not work directly against your existing data. You may want to extract those records that do not have their individual components separated out into the individual fields and attempt to do a batch address match on the Street field. Either that or just use the entire table--those without unit info should match properly. You would need to build a locator using the style above so that the batch geocode process will recognize and "slice-and-dice" your address into the proper pieces.

You would need to create a new feature class that includes the extra fields to hold the building/unit attributes and load the address match result into it, or simply use the batch match result 'as-is'.

I am likely leaving out some details but hopefully you can follow the thought process.

Re: Standardize addresses tool

JoeBorgione — Mon, 02 Dec 2013 17:25:49 GMT

Personally I'd import the spreadsheet into a geodatabase table; that's where I prefer to work. It looks like your street field has a comma between the actual address and the bldg/apt reference. With the split() function in VB you can put the bldg/apt reference into it's own field and the actual address in its own field.

Add two fields, both text. Call one TheAddress and the other TheAptBldg

Calculate TheAddress:

dim list
dim addr

list = split([STREET],","]
addr = list (0)

Calculate TheAptBldg

dim list
dim aptbldg

list = split([STREET],","]
aptbldg = list (1)

Untested. Your mileage may vary...

Also; you might want to look into cleaning up your data: I go with all caps, and NO punctuation so St. becomes ST etc etc. Use USPS street type standards. Don't re-invent the wheel.