Splitting Cell Data
 

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?

Easiest way is to select/highlight all of your data in column A. Click on
Data/Text To Columns. Select Fixed Width. You will see a Data Preview
Screen w/vertical lines. These vertical lines will be column breaks. You
can drag them to right/left, remove them by doubleclicking on the line, or
put one in by a single click.

Put breaks in as follows

City, State|,|Zip

Now, after you click next, select the column with the comma in it, and
select the "Do not Import" option. Then click finish.




"Zip Codes" wrote:

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?

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?

Hi!

That would only work as long as the

City, State|,|Zip

data to separate is all equal in length and the second comma is in the same
location in every cell. I'm assumming that the real data looks like this:

Columbus, Ohio, 44452
Beaver Falls, Pennsylvania, 15010
Fresno, California, 95488

In which case T to C fixed will not work. Of course, you could use T to C
delimited by comma and end up with 3 columns.

Biff

"JMB" wrote in message
...
Easiest way is to select/highlight all of your data in column A. Click on
Data/Text To Columns. Select Fixed Width. You will see a Data Preview
Screen w/vertical lines. These vertical lines will be column breaks. You
can drag them to right/left, remove them by doubleclicking on the line, or
put one in by a single click.

Put breaks in as follows

City, State|,|Zip

Now, after you click next, select the column with the comma in it, and
select the "Do not Import" option. Then click finish.




"Zip Codes" wrote:

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?

Yeah, I was asleep on that one.


"Biff" wrote:

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?

If they all have a comma separating Town, State, and zip you could use
the Text to Columns under Data to separate the data into 3 columns. Then
use =A1&","&B1 to put the town and state back together in one column.

Bob

Zip Codes wrote:
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?