|Here's the problem:|
With 800 rows, I am assuming that you are going to have names with 2 words, as well as names with 3 (or more) words:
Smith Jr. Steve
Spencer Mary Anne
In addition, I'll bet you'll have multiple formats for the addresses:
16 Maple Ave
25 East Main St
6 West Lane
1232 East Palm Leaf Circle
Your example already showed different formats for cities, 1 word vs. 2:
West Hartford CT
While there are text functions such as LEFT, RIGHT and MID that can extract parts of those strings, there is no way - I'll say it again - no way that any single formula or Text to Columns operation can deal with all those different formats of names, addresses and cities. There is nothing for Excel to "trigger on" to be able to tell what is a name, an address or a city.
Just as one simple example let's take this string:
Jones Mary 166 New Lane Dallas TX 11111
This formula will produce Jones Mary:
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)
In fact, this formula will work for any name that contains 2 words.
However, if you had this:
Jones Mary Jo 166 New Lane Dallas TX 11111
The formula would still return Jones Mary which not only would be incorrect, but any formula that we write to extract the address that starts after the name would be starting in the wrong position.
Trust me, I deal will mailing lists all the time - some of them thousands of rows long - and there is no "one size fits all" formula or operation that will extract the various parts that you are looking for - except, of course, if every name contains the same number of words, every address contains the same number of words and every city contains the same number of words. Good luck finding a list that meets those criteria!
As I said in Response #2, it's going to take a combination of operations and formulas to get the data set up the way you want.
Posting Tip: Before posting Data or VBA Code, read this How-To.