User1282637 asks if there is a way to accomplish this task and requests any help in doing that. The issue is not Excel mechanics as much as how to deal with the ambiguity in the data. Parsing the ZIP Code and description are straightforward (unless you have a mix of 5 digit and 9 digit ZIPs). The difficult problem is in separating the street from the city, so I'll focus on that. This is not intended as a step-by-step how-to-do it with Excel formulas. It is just sharing some insight into the problem and describing an approach for getting to a result for the hard part.
The problem is that there are no delimiters between the different fields. That is not an issue for peeling off the description or ZIP because those are easily identifiable. The problem is determining where the Street ends and City begins. Consider these variations in the Street portion (far from an exhaustive list):
120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3
The number of "words" in the street can vary from as low as 1 or 2, to as many as 7 or 8, so that isn't useful for parsing. The street "type" also isn't especially useful. There are on the order of 50-100 words used just for the street "type" (street, avenue, boulevard, way, turnpike, lane, court, circle, terrace, etc.). Combine that with the use of abbreviations for the street type, both proper and incorrect, and the list is in the hundreds. Plus, this designation isn't always the last word in the street field. The street is the hardest portion to identify so the logical approach is to identify the rest and then the remainder is the street.
The city can be several words. Washington Court House, OH is three words. Then consider situations like St Marys, OH. Is the "St" a part of the city name or a street type designation; which field does it go in? Or South Euclid, OH--is "South" part of the city name or a directional that is part of the street address? City has its problems but there is a way to deal with them.
Even using the ZIP code to identify the city has problems. There isn't always a 1:1 match between city name and ZIP.
The most practical way to attack the problem is to use "dictionaries": a list of cities, and a ZIP Code directory. These are the most unambiguous portion of the address. These can be found online or from the Postal Service. To do comparisons, you may need to clean up either your data or the listings. They will need the same capitalization style and any extra spaces in your data will prevent an exact match.
If either your data or the listing uses abbreviations, you will need to deal with that. Either translate the unabbreviated one to the standard abbreviations or do a secondary match against an abbreviations dictionary (also available online or from the Postal Service), when those differences are found.
The ZIP can be readily parsed, and that is a good place to start. Do a ZIP Code lookup against the ZIP Code directory. If the result exactly matches a string of words immediately preceding the ZIP, that identifies what portion of the record is the city field.
If there is not an exact or unambiguous match, move to a comparison of city names. Iterate through the city names list. For each name, determine the number of words it contains and compare it to that number of words immediately preceding the ZIP.
If you get a match through either process, everything remaining to the left of the city is the street address.
This type of application is much easier to do with a database application than with a spreadsheet. Either way, you can see that trying to do this in an automated way is not a simple task. You can't do it with a few spreadsheet formulas.
No matter how rigorous your programming is, you are likely to still have records you need to parse by hand, and parsing errors you need to correct by hand. You don't indicate how many records you have. It may be less work to simply do it manually.
If the number is large and I had to do it, I would cull the list. Match the records that are easy, like unambiguous ZIP matches. Then let the quantity of data govern how far you go programming automated matches.
For the records that are left, assuming you have already peeled off the ZIP and description, here is a way to speed up the manual process. Look at the record and visually identify the number of "words" in the city, which is a fast mental task. Enter that in a prescribed cell and use a formula to split the street from the city based on the number of space breaks (separate at the Nth space where N = total spaces + 1 - number of words in the city name).