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.
- If there are multiple addresses or PO boxes across the row, append these into separate address rows.
- 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 |
| | |
| | |
- 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
- 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
