My download tool takes a list of common street abbreviations from the USPS website (https://pe.usps.com/text/pub28/28apc_002.htm), finds the position of the street address, then works backwards using the trim function to pull ZIP (if appended), then State, then City.