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

>.
>

[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

"NM_Frenchman" <anon...@discussions.microsoft.com> wrote in message
news:51ADC434-C738-42E8...@microsoft.com...

[Harlan Grove]

"Jason Morin" wrote...

>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.

--
To top-post is human, to bottom-post and snip is sublime.