Hi!

The problem is i can only do this for individual cells so i would have to
do A1, A2, A3 and so on.

Simply drag copy or, it can be as easy as double clicking the fill handle.

Try this....

Assume your data is in A1:A900 with no empty cells within that range.

In B1 enter this formula:

=RIGHT(A1,5)

In C1 enter this formula:

=SUBSTITUTE(A1,", "&B1,"")

Select both B1 and C1.

Double click the fill handle to copy the formulas down to row 900.

Inspect the results to make sure they're correct. When satisfied select both
columns B and C. Right click within that selected range and click Copy. Then
right click within that range again and select Paste Special ValuesOK.

Now, if you want, you can delete column A and then drag column C over to be
the new column A.

Biff

"Zip Codes" wrote in message
...
I have about 900 cells and all of them appear in the following way
A
Town, State, 99999
Town1, State1, 11111

I need to have the zip code (99999 in the example) in one column, and then
town and state in another column so it would look like this
A B
Town, State 99999
Town 1, State 1 11111

I've been experimenting with the RIGHT and LEFT formula-- =RIGHT (A1, 5)
which would give me the zip code. The problem is i can only do this for
individual cells so i would have to do A1, A2, A3 and so on. Is there a
way
to write a macro or formula to get all of the cells to transfer over
without
having to type A1 over and over?