extract address information from one cell into adjacent ones

[dgr7]

hello,
I have a new client that just gave me a large number of accounts to process into our database, and in our system when I input them electronically I need the address, city, state, and zip into separate fields in the .txt file I export from excel, however, this client has given me the address information all in one cell
does anyone know a formula and/or some VBA that can look at for example cell A1 with the street address city 2-character state & 5-digit zip all in the one cell and extract the data into the nearby columns, so that the address would be in B1, city in C2, state in D2 and zip in E2, and the address and city could be a variable number of words?

some examples:

a1 has
123 future past rd anytown sc 29424
b1 would contain
123 future past rd
c1
anytown
d1
sc
e1
29424

a2 has
342 roland hwy apt b2 another place sc 29432
b2 would contain
342 roland hwy apt b2
c2
another place
d2
sc
e2
29432

thanks in advance,
david

 

[dgr7]

can anyone help me with this?
thanks in advance,
david

 

[mikerickson]

In E1
=RIGHT(A1,5) should return the zip code
In D1, (there must be a shorter version) this will return the state.

=MID(TRIM(SUBSTITUTE(A1,E1,"")),FIND("x",SUBSTITUTE(TRIM(SUBSTITUTE(A1,E1,""))," ","x",LEN(TRIM(SUBSTITUTE(A1,E1,"")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,E1,""))," ",""))))+1,100)

But I can't figure out how to tell when the City starts. The space in Another Town is a problem.

 

[dgr7]

mike,
hello,
thank you for the formulas for the state & zip.
obtaining the street address and the city have been the most challening parts of this problem that I've been hoping someone would know an answer to. unfortunately the data has no delimiter in it separating the street address from the city from the state from the zip...just one space between each word.
thanks,
david