Text Parsing??

Let me just say that I greatly appreciate the help I've received here over the last couple of days. Such a knowledgeable group and gave a total VBA noobie exactly what I was looking for. With that, I have another question.

I have address information in one column that I need to parse out into new columns. Basically, I have a city state and zip with no delimiters other than spaces in one cell, and I need it to be in separate cells.

Current Data Format:
Row 2: |Granite Bay CA 95746|
Row 3: |Galesburg IL 61401|
Row 4: |Waukee IA 50263|

What I need is this:
|Column 1| |Column 2| |Column 3|
|Granite Bay| |CA| |95746|
|Galesburg| |IL| |61401|
|Waukee| |IA| |50263|

I know that my zip code is going to always be 5 characters long, and the state is going to be 2 characters long and was thinking of somehow working backwards to get the zip where it needs to be first, then the state, and leave the city alone since it wouldn't need to move in theory.

Is there any way to do this in VBA or am I just over-complicating what I'm needing done?

Thanks in advance!

Once again, you folks have come through for me. Thanks a ton!

Originally Posted by sweep

Hi,

Presuming your data starts at A1, put the following in B1, C1, and D1 to parse the text.

=LEFT(A1,LEN(A1)-9)
=MID(A1,LEN(A1)-7,2)
=RIGHT(A1,5)