Challenge #54: Data Prep Address Parsing

[varunvarma87]

Challenge 54 completed 

Spoiler

Had to fix street (example- Circle, Street, Drive, etc...) on regex. 
 

[SueDonim]

Good parsing practice.  Of course, there could be a problem if the City name started with "St" as in "St Louis" - would be hard to differentiate between "Street" and "Saint", though I suppose you could look for two consecutive instances of "St" in that case

Spoiler

Was struggling with figuring out how to do a switch, when saw idea about replacing text with a pipe.  Instead, I changed all street types (e.g. Avenue, Circle, etc) to Street.  Then it was a simple parsing from that point.

Probably should learn RegEx....

[paulburrows]

Deffo need more regex experience!

[Billigans]

I believe this can be done with a single RegEx tool. 

Spoiler

I ended up only using regex to get the data that I needed by parsing and removing case sensitivity.

^.+(Circle|Street|Drive|Road|Ave|Avenue|St)\s(.+)\s(\u{2})($|\s\d{5})

If anyone has any idea how to not have to use the street identifiers as a marked group as well as getting the extra space in the address I'd be very interested. If you can do that you can complete this challenge with a single tool.

[JennyMartin]

Spoiler

Looking forward to seeing solutions with fewer tools!

[justindavis]

Spent a lot more time fiddling with the RegEx than I'd have liked, but the good thing is after stopping to figure out why it wasn't parsing the Zip the way I wanted, I had a good takeaway that will continue to help me as I think through RegEx going forward!

Spoiler

[mat_budden]

Hi All,

Please find my Result below.

Spoiler

Just used Formula's and Text to Columns, Not Terribly Efficient, But it works.

Thanks

Mat

[RoDO]

My solution with durth regex

Spoiler

(Circle|Street|Drive|Road|Ave|Avenue|St)\s(.*)\s([A-Z]{2})\s?(\d{5})?