I have a zip code column that has both standard zips (5 digits) and zip +4
(00000-0000). When I sort by zip it sorts all the 5-digit zips separately
(at the beginning) from the zip+4. How can I get it to sort and mix them
together in proper order?

Create 2 side by side helper columns, one for the 5 digit zip prefix and
another for the 4 digit suffix. For the example I'll assume that the zip
codes start in D1:

In the first helper column enter the following formula to extract the 5
digit zip:

=LEFT(D1,5)

In the second helper column enter the following formula:

=IF(LEN(D1)=10,RIGHT(D1,4),"")

The IF statement only gets the 4 digits if the zip is 10 characters long.


Copy both formulas down to the last address in your table.

Now when you do your sort sort by the 5 digit zip formula column as your
primary and the 4 digit zip formula column as your secondary.

Hope this helps.
--
Kevin Backmann


"JWCrosby" wrote:

I have a zip code column that has both standard zips (5 digits) and zip +4
(00000-0000). When I sort by zip it sorts all the 5-digit zips separately
(at the beginning) from the zip+4. How can I get it to sort and mix them
together in proper order?

You want to format the column as text (string data), to fix what you have, see
Rearranging Data in Columns (join.htm), subtopic ...
Fix up for 5 digit US zip codes (#fixUSzip5)
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

You can enter zip code as 09876- and it will be a text,
but formatting the column ahead of time as text is best.
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"JWCrosby" wrote...
I have a zip code column that has both standard zips (5 digits) and zip +4
(00000-0000). When I sort by zip it sorts all the 5-digit zips separately
(at the beginning) from the zip+4. How can I get it to sort and mix them
together in proper order?