Split address into multiple cells

[Rodney Jorgensen]

I need some help with either formulas or vba code to take a City, State, ZIP from one cell and split it into 3 cells.

So, Say I have this is Range("A1") = Rio Vista CA 94571-0157
how can I split this into 3 cells, say B1:D1?

I do believe I will always be working with the zip+5 and always the 2 digit state code.

Thanks for your help.

 

[lenze]

For B1

=LEFT(A1,LEN(A1)-11)

For C1

=MID(A1,LEN(A1)-12,2)

For D1

=RIGHT(A1,10)

lenze

 

[Rodney Jorgensen]

This was the path that I was thinking also. I forgot the use of LEN to accomplish what I needed. Thanks so much. By the way a change to B1 was needed because it pulled the state also, not just the city. I canged it from -11 to -14.