Most of this can be done with regular expressions.

The problem I run into is to define the delineation between street and city.

Zipcode is easy, its either ddddd or ddddd-dddd at the end of the line.

The State is easy, too.  It will be a two-letter string prior to the Zip code.

The City will be the string with the street, state and zip removed.

But Cities can be two words, or hyphenated.  And streets can be any number of
words.

You can come close with the following:

1.  Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

You can try the following formulas, with your string in A2

Zip:
E2:    =REGEX.MID(A2,"\d{5}(-\d{4})?$")

State:
D2:    =REGEX.MID(A2,"\b\w{2}(?=\s\d{5}(-\d{4})?$)")

Street: (This is the tough one; you may need to add some more options to the
alternation; if you do, note that each dot '.' must be preceded by a back
slash).  This won't get the streets that have just a name, though.  Like 166
Broadway.  But you should be able to pick them out as the street and city will
be merged into the city column; and the street field will be blank.

B2:
=REGEX.MID(A2,".*(St\.|Street|Ave\.|Avenue|Blvd\.|Boulevard|Place|Pl\.)")

City:  Just remove what we've already found:
C2:    =REGEX.SUBSTITUTE(A2,"\s?("&B2&"|"&D2&"|"&E2&")\s?")

--ron