I currently have an account with Overstock.com and when i export orders from their site they place shipping information in the same cell so that it looks like this.


Mariah North 108 W Park St Hallettsville, TX-77964


I clean the cells using Ctrl + H replacing multiple spaces with a single space so that it looks like this.


Mariah North 108 W Park St Hallettsville, TX 77964

Now I want to create a formula that extracts the Name, Address, Apt/Unit #, City, State, Zip Code. So I use Left, Mid, Right. Here is a sample code.

=LEFT(G2,FIND(" ",G2,1+FIND(" ",G2))-1) This extracts the name but only of a person who does not use a middle name

=MID(G2,FIND(" ",G2,1+FIND(" ",G2))+1,FIND(" ",G2,FIND(" ",G2)+1)) This extracts address but not if it is a street with more then one name ie 108 W Park Hedge St

does anyone have a better way to do this so that i can record a macro.