simple sorting of zip codes

[billrebels4]

Hey everyone-

I am trying to do a simple sort of around 15,000 zip codes. THe problem
is that it wont SORT!!!!! i imported my mail list from a text
file(comma delimited) and then tried to sort my zip codes but nothing
will happen. The zips are mostly Zip+4 with some regular 5 digits
sprkiled throughout. The numbers line up on the left side of the
column. I dont know what i am doing wrong... Can anyone help me
please... Thanks, BR4

 

[Ken Wright]

Hi Bill - and just when you thought you were there eh!!!

Give us an example of some of the zips and tell us what is not sorting as you expected it
to. Show us what you have and what you expected to happen when you sorted.

 

[billrebels4]

KEN_ how right you are.... this "little" project is taking me days to
finish instead of hours...

i used a formula someone on here gave me to make them all 5 digit
zips... i think it is going to fix my problem.. Thanks for all the help
in here... i work for a very small publication and am the best computer
guy around here so all these tasks seem to fall onto me... i guess i
should have paid more attention in MIS classes in school....

Look for my posts later... i am sure there are more to come...

That solution you gave me yesterday will eventually save me hours of
work!!! if i can ever get to where i can actually use it... lol... it
works well i just am trying to get my data worked out....

THANK YOU THANK YOU THANK YOU!!!!!!!

 

[Ken Wright]

LOL - No problem - Are you sure you are sorted with the zips - Your question seemed to go
up a number of times, and theer have been numerous answers to it. It really is a simple
fix, so just holler if you need a hand. Routing numbers can be stripped off the end
easily, and all zips can be standardised to 5 digits.

 

[David McRitchie]

If sorting on the zip code is all you want they should all be converted
to text then the post office will be happier with 9 "digit" zip codes where
available. To that end I have a macro FixUSZIP5
http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5
which will make everything text 5 digits converted to text, and the
9 digit with the hyphen are already text.

If you want a worksheet solution using a helper column to
actually create 5 characters of text from the zip code.
=LEFT(TEXT(F15,"[<100000]00000_-_0_0_0_0;[>0]00000-0000;;@"),5)
should handle any US zip code you throw at it.

 

[David McRitchie]

For a worksheet solution what I really wanted to stress was that you
can sort mixed US 5 digit zip codes and US zip+4 zip codes with the
helper column with the formula:
=TEXT(F2,"[<100000]00000_-_0_0_0_0;[>0]00000-0000;;@")

and I have updated my webpage accordingly:
http://www.mvps.org/dmcritchie/excel/join.htm

Needless to say I prefer the macro solution, to fix things once, where
they reside with the formatting preentered as text.

If sorting on the zip code is all you want they should all be converted
to text then the post office will be happier with 9 "digit" zip codes where
available. To that end I have a macro FixUSZIP5
http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5
which will make everything text 5 digits converted to text, and the
9 digit with the hyphen are already text.

If you want a worksheet solution using a helper column to
actually create 5 characters of text from the zip code.
=LEFT(TEXT(F15,"[<100000]00000_-_0_0_0_0;[>0]00000-0000;;@"),5)
should handle any US zip code you throw at it.