Splitting data

**Bhelms** · 01-13-2009, 12:28 AM

I need to split data into 2 columns.

For instance...

City, state 29785
city, state 29767
city, state 29719

I need the zip codes to be pushed to the next column, to column 'D' to be exact. I already tried using the "text to columns" function. I selected "delimited" file type and used "2". it pushed the data to the next column everytime there was a 2. The problem is, there are 2's in the middle of the zip code sometimes. Also, it gets rid of all the 2's. Also, I can't use the "fixed width" option because there are some city/states that have more letters in them obviously, so the column is not a perfect width, if that makes any sense. Any help would be greatly appreciated as this is a column with 6,062 rows so it will be tough and tedious to do it all by hand.

**mewingkitty** · 01-13-2009, 12:33 AM

Please Login or Register  to view this content.

If your info starts in a1, and copy it down as far as you need to.

Takes the 6 right-most characters from a.

If you need to split them permanently we can do that too, just lemme know.

**Bhelms** · 01-13-2009, 12:55 AM

How do I use that? And its all in Column C. I need the city, State to remain in C and the zip codes to go to column D.

**mewingkitty** · 01-13-2009, 01:15 AM

See attachment.

I've got some instructions on there, try it on this worksheet, I'm not sure if the one you're working on is set up in such a fashion that it'll interfere with how I have this done.

Oh, yeah, almost forgot.
Just press the big button.

mew.

**Bhelms** · 01-13-2009, 01:25 AM

I did what it said, But maybe I am missing something. It just copied the same zip code from C1 to each row in D. I attached a file. I need all those zip codes in the next column, but also deleted from the original column. One issue is, a few rows say "serving the SC area". However, thats not a huge problem if they get moved over cause I can just go back and fix that manually since its just a few I believe. If I am just missing something about your original fix, lemme know and I can give it another try.

**Bhelms** · 01-13-2009, 01:28 AM

Nevermind. Your edit fixed everything. That works great. I will just go through and move the "serving the SC area" text back in place unless there is a simple way of making it only move numbers.

**mewingkitty** · 01-13-2009, 01:37 AM

I can make it do pretty much anything, I just need to know what it's supposed to be doing. I wasn't aware of a "serving the sc area" anywhere in the strings I was working with.

EDIT:

Attached is your full list with the "serving sc area" intact and all zip codes seperated.