Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
New content is available in Academy! You may need to clear your browser cache for an optimal viewing experience

Challenge #54: Data Prep Address Parsing

Highlighted
Asteroid

uploaded

Highlighted
Asteroid

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
Zenon_0-1579794174017.png
Asteroid
Spoiler
Spoiler
ImranA_0-1579863060180.png
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  

Highlighted
Asteroid

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

Highlighted
Asteroid

My solution using RegEx 

Highlighted
Asteroid

Thanks GeneR.

Highlighted
Asteroid

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

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

Challenge#54_Image.png
Highlighted
Spoiler
sonyakasenkramer_0-1580395275980.png
Highlighted
Alteryx Certified Partner

Thanks for the challenge, here's my solution

Highlighted
Alteryx Partner

Could only parse ZIP and states.

Didnt' have the required level to get only the city