Challenge #54: Data Prep Address Parsing

[liamholland]

I used this challenge to get to grips with how to use Google's Geocoding API to download address information. Great practice and a shoutout to @mceleavey whose step-by-step guide is perfect.

Spoiler

[BSilverblatt]

Thanks for the challenge! Here is my solution:

[jatterberry]

Added a filter to highlight addresses w/o a zip

Spoiler

[jgosalia]

Spoiler

[sumanthskumar]

Number 13

Had to use words like St, Street, Ave etc to get the city name

Spoiler

[Emil_Kos]

Hi,

Sharing my solution.

I saw that there are much more efficient ways of doing this but I will stay with my solution. 

Spoiler

[DMContente]

My solution, from a while ago

[edavilac]

Loved this challenge! I don't know how to use Regex formulas, so I used "normal" formulas.

Spoiler

I used 4 formulas:

ZIP Code:

IF EndsWith([Address Text], "0") OR
EndsWith([Address Text], "1") OR
EndsWith([Address Text], "2") OR
EndsWith([Address Text], "3") OR
EndsWith([Address Text], "4") OR
EndsWith([Address Text], "5") OR
EndsWith([Address Text], "6") OR
EndsWith([Address Text], "7") OR
EndsWith([Address Text], "8") OR
EndsWith([Address Text], "9")
THEN Right([Address Text], 5)
ELSE Null()
ENDIF

State
IF IsNull([ZIP Code])
THEN Right([Address Text],2)
ELSE ReverseString(Substring(ReverseString([Address Text]),6,2))
ENDIF

City (Part 1)
IF IsNull([ZIP Code]) THEN
ReverseString(GetWord(ReverseString([Address Text]), 2))+" "+ReverseString(GetWord(ReverseString([Address Text]), 1))
ELSE
ReverseString(GetWord(ReverseString([Address Text]), 3))+" "+ReverseString(GetWord(ReverseString([Address Text]), 2))
ENDIF

City (Part 2)
IF StartsWith([City], "Street ")
THEN TrimLeft([City],"Street ")
ELSEIF
StartsWith([City], "St ")
THEN TrimLeft([City],"St ")
ELSEIF
StartsWith([City], "Ave ")
THEN TrimLeft([City],"Ave ")
ELSEIF
StartsWith([City], "Avenue ")
THEN TrimLeft([City],"Avenue ")
ELSE
[City]
ENDIF

[deviseetharaman]

Spoiler

[mhou]

🙂