Help Address scrubbing and parsing

[lruchlin]

Hello All,

I am having some trouble scrubbing and parsing some address fields. Is there a standard or good process to do this?

Attached is a sample spreadsheet with Addresses. Some of the addresses are split out by column and some are not

Group_id

MLINE1

MLINE2

MLINE3

MCITY

MSTATE

MZIP

Below are some requirements for address scrubbing.

1. If there are multiple addresses or PO boxes across the row, append these into separate address rows.
2. Once step 1 is done, then scrub and parse the data in the columns as follows per these rules. The below table will show you what types of data should be in what column

MLINE1	MLINE2	MLINE3
Street address	Suite	C/O
PO box	Apt	T/A
Rural Route	Building	Attention
State Route	Floor	D/B/A
		Correspondence

1. All common elements on any address field should be condensed

Examples:

Street to ST

Circle to CIR

Road to RD

Avenue AVE

Lane to LN

Suite to STE

Floor to FL

1. Any records that do not match would have to be split out into a excel spreadsheet so we could do manual scrubbing.

Thanks,

Lloyd Ruchlin

[scottj · Accepted solution]

I approached this by transposing all the data by the group, then using some formula/multi row formulas to create the multiple rows.  Once the data was in a good state, I used the Address Parse tool to separate all the pieces.  See attached for my example and a place to start.