Sorting of mixed zip codes (5 digit and 9 digit) in ascending order

[NM_Frenchman]

I have a large mailing list in FileMaker Pro which I export to Excel so that a printing house can take the Excel address mailing list, with the addresses sorted in ascending zip code order, to do a mail merge with the Word document (brochure). Essentially, they print the brochure and the mailing address in one operation.

Unfortunately, unlike FileMaker Pro Excel cannot properly sort a mixed zip code (5 digit and 9 digit zip codes) in proper order. It seems that no matter how one formats the cells, Excel will separately sort all the 5 digit zip codes in order and all the 9 digit zip codes in order (i.e., all the 5 digit codes first followed by all the 9 digit codes). Does anyone know how to make Excel properly sort a mixed zip code field?

 

[Jason Morin]

Create another column that extracts the first 5 digits
using:

=LEFT(A1,5)*1

and then select both columns and sort ascending based on
this new column.

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a large mailing list in FileMaker Pro which I

export to Excel so that a printing house can take the
Excel address mailing list, with the addresses sorted in
ascending zip code order, to do a mail merge with the Word
document (brochure). Essentially, they print the brochure
and the mailing address in one operation.

Unfortunately, unlike FileMaker Pro Excel cannot properly

sort a mixed zip code (5 digit and 9 digit zip codes) in
proper order. It seems that no matter how one formats the
cells, Excel will separately sort all the 5 digit zip
codes in order and all the 9 digit zip codes in order
(i.e., all the 5 digit codes first followed by all the 9
digit codes). Does anyone know how to make Excel properly
sort a mixed zip code field?

 

[Peo Sjoblom]

Make sure they are all text, that works for me. If you mix them the numeric
will sort first and
you need to have them all text.

--

Regards,

Peo Sjoblom

I have a large mailing list in FileMaker Pro which I export to Excel so

that a printing house can take the Excel address mailing list, with the
addresses sorted in ascending zip code order, to do a mail merge with the
Word document (brochure). Essentially, they print the brochure and the
mailing address in one operation.

Unfortunately, unlike FileMaker Pro Excel cannot properly sort a mixed zip

code (5 digit and 9 digit zip codes) in proper order. It seems that no
matter how one formats the cells, Excel will separately sort all the 5 digit
zip codes in order and all the 9 digit zip codes in order (i.e., all the 5
digit codes first followed by all the 9 digit codes). Does anyone know how
to make Excel properly sort a mixed zip code field?

 

[Harlan Grove]

Create another column that extracts the first 5 digits
using:

=LEFT(A1,5)*1

and then select both columns and sort ascending based on
this new column.

...

Thus potentially getting results like

55555-2151
55555-3798
55555
55555-1234
55555-9876
55555
55555-5555

since there's no guarantee that the underlying column would be sorted in order
by the last 4 digits when present. Presumably the OP needs to have mailing
labels correspond to bulk mail bar coding or suchlike, otherwise there'd be no
need for any sorting at all. If the OP needs zip codes sorted as (using the
preceding sample)

55555
55555
55555-1234
55555-2151
55555-3798
55555-5555
55555-9876

then the OP *MUST* convert the zip codes to text and sort them as text.