Zip code sort is an Excel problem even when using the special cat.

[Guest]

Zip code sorting is a problem using excel 2003. I have zips both in 4 and 9
digit format. I have tried every conceivable format and even followed the
recommendation online by typing in the custom category
[<=99999]00000;00000-0000.
I have tried to retype in the zip codes but that doesnt seem to help.
Note that some info comes in from older excel versions and from Word docs.
Can anyone help?

 

[Guest]

I only use 4 digit zip coding, but it has always worked very well when
sorting. I use a Custom format of 0000, so that 0001 will show like that.

 

[Dave Peterson]

If the problem is the sorting, it's probably because excel sees your entries as
numbers.

9 digit zip codes of
123456789
234567891
and five digit zipcodes of
12345
23456

Will sort in this order:
12345
23456
123456789
234567891

Just because 12345 (12,345) is much smaller that 123456789 (123,456,789).

If you want to sort it so
12345
12345-6789
23456
23456-7891

then maybe you could use a helper column and convert them text with a formula
like:

=TEXT(A1,"[<=99999]00000;00000-0000")
or even:
=a1&""

Then copy down.

Now sort your range by that helper column. (Hide it/delete it when you're
done.)

Zip code sorting is a problem using excel 2003. I have zips both in 4 and 9
digit format. I have tried every conceivable format and even followed the
recommendation online by typing in the custom category
[<=99999]00000;00000-0000.
I have tried to retype in the zip codes but that doesnt seem to help.
Note that some info comes in from older excel versions and from Word docs.
Can anyone help?