excel zip code sort (zip and zip+4)

[Jeff]

I'm afraid I'm a bit of an Excel newbie, but I can't figure something simple out.

I have an excel file that needs to be sorted by zip for mailing, but the data contains 2/3 of the zip codes in the form 49720 and the other 1/3 in the form 49720-2345. The zip codes are sorted first, and then following are the zip+4 codes, also in order, but not ordered with the other zip codes.

Is there an easy way to get Excel to sort based on the first five digitis only? Or some other quick fix?

[Jarrod]

Think the easiest way is to add a new column that takes off the first 5 characters. Assuming the zip or zip+4 is in cell a1, formula in cell b1 would be =mid(a1,1,5). Then you will can just on the zip part.

Jarrod

[ryount]

I just tried it, it gives me two options because of the mixed data. if you tell it to sort "numbers and numbers sorted as text separately" it works correctly. I am guessing it isn't prompting you for that though. Not sure what version of Excel you are using, but I don't think it did that until Office XP. Jarrod is on the right track but then you might not get the zip+4 extension sorted. You could create a new column for sorting to append 0000 to the missing ones with something like this:
=IF(LEN(A1)<6,A1&"-0000",A1)
Where A1 is the cell that has the zip code. Then copy it down.

[Randall]

Another approach: Select the whole column and do a Data > Text to Columns. Choose "Delimited," and for the delimiter select "Other" and type a hyphen in the box.

That will give you two columns, one for the zip and another for the +4.

Randall

[Jeff]

Thank you very much Jarrod, ryount, and Randall, as always.

I'm running office 97 still, because I'm cheap and don't stress it much at all. I suppose as I start using excel a tiny bit more I might have to upgrade one of these days :)

This worked great and I now have the +4 part of the newer zips in a new column. I only need to sort into zip code zones, so I don't even need to sort by the +4, but at least now I know how if the time comes.

Thanks again to everyone! Much appreciated.

[ryount]

I wouldn't bother upgrading Excel. I really can't think of that much that has improved since 97. There's only so many bells and whistles you can actually use. I would still be using Lotus 123 but the later versions are terrible. I think the / commands in 123 are so much easier than using a mouse in Excel.

[Randall]

I wouldn't bother upgrading Excel. I really can't think of that much that has improved since 97. I've used some newer versions of Excel on other people's computers, but I'm still on 97 myself. Word 2000 is somewhat worth the upgrade, and I think I'd appeciate Access 2003 more if it had a @#$% Most Recently Used menu like any normal Office program...

I can think of plenty of annoyances introduced with Office 2002/2003, but no positives to speak of.

Randall

[ryount]

Most Recently Used menu like any normal Office program...

Funny, that's one of the things I find annoying about the newer versions of Office. I always turn it off.

[Randall]

Funny, that's one of the things I find annoying about the newer versions of Office. I always turn it off. Well, as I just learned this evening, the "most recently used" list does exist -- in the Task Pane (http://www.socialsciences.manchester.ac.uk/postgraduate/it_skills/images/Module6/taskpane.jpg). And like you, I turned it off immediately after installing the program.

I spend 99% of my Access time in just two databases, so it's really been slowing me down, even with the database folder added to my "places" list (http://www.uncc.edu/sysdev/HowTos/Excel/Easy%20Folder%20Access%20in%20Office%20XP_files/image003.jpg). Access 97 opened up with a sort of task window (http://www.wellesley.edu/Computing/Access/images/Image1.gif) which included the MRU list. I'd select a file to open and then it went away. A very efficient use of my time and screen space. But not anymore...

Another reason to hate the @#$% Task Pane. :grr:

Hmmm. Maybe I can set up a custom icon for each db file and stick them in the Start menu...

Randall