Challenge #54: Data Prep Address Parsing

[jdbartosh]

uploaded

[ZenonH]

I followed a more manual approach by cleaning/standardizing the street names, and then removing easily parsed fields (state/zip) from the original field. With less to parse it was easier to isolate city

Spoiler

[ImranA]

Spoiler

Spoiler

Spoiler

First formula - ZIP - Tonumber(Right([Address Text],6))
State - Right([Address Text],9)

Second formula - STATE - IF [ZIP] = "0" Then Right([State],2) else [State] endif
CITY - Replace([Address Text], [ZIP], " ")

Text to Columns - split to 3 columns (leave extra in last) CITY - "SPACE" 

Third Formula - CITY3 -  Left([City3], Length([City3])-3)
Word Count - CountWords([City3])
City - IF [Word Count] = "3" Then Replace([City3], GetWord([City3], 0), " ") else [City3] Endif
ZIP - "0" + [ZIP]
ZIP - If [ZIP] = "00" then Null() else [ZIP] Endif
 

I didn't like the idea of adding a text input and forcing a delimiter so I found a way around it 🙂 

[mikeprice17]

Fairly similiar to the provided solution except I parsed out the data in formulas rather than the parsing tool.

[AngelosPachis]

My solution using RegEx 

[dashakir1]

Thanks GeneR.

[PeterH23]

My solution. Took a while, but I was quite pleased with this one.

Spoiler

3 separate regex parse tools (after reading up about regex).

[sonyakasenkramer]

Spoiler

[chris_ramsay_dup_425]

Thanks for the challenge, here's my solution

[JeremyGonzva]

Could only parse ZIP and states.

Didnt' have the required level to get only the city