# 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

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
Asteroid
Spoiler
Spoiler
Spoiler
First formula - ZIP - Tonumber(Right([Address Text],6))

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

Highlighted
Asteroid
Spoiler
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