sorting zip codes

[MicheleB]

I am trying to sort 5 digit zip codes and 5 digit +4 zip
codes. It is putting 9 digit zips at bottom instead of
collating with 5 digit. Any suggestions?

 

[Dave R.]

This might help. The 5 digit zip codes are probably being seen as numbers,
and the 9 digit are definately seen as text if they have a "-" in between.

I tried on a sample range formatting all as text, but that didn't work. I
also tried pulling over the numbers into text format by using TEXT(A1,0) and
that didn't work.

I then tried manually adding a ' before each of the zip codes, and that
worked. I don't know how to do a formula which will automatically "add" the
worksheet-invisible ' character which means they are being seen as text
strings.

Maybe someone knows a way to do that or another solution for you.

 

[Felipe]

Michele,

Two suggestions:
1. Write the 5 digit + 4 codes with a dot separating them
(e.g. 12345.1234) This will sort them as decimals and
place them correctly. If your codes are already entered as
numbers you can use:
Assuming the first code is in A1
=IF(LEN(A1)=9,A1/10000,A1)


2. Divide the zip code in two columns and sort them
considering both:
In column B: =LEFT(A1,5)
In column C: =IF(LEN(A1)=9,RIGHT(A1,4),0)

Regards,
Felipe

 

[Don Guillett]

Have a look here
http://tinyurl.com/ry0p

 

[David McRitchie]

Hi Michele,
I would suggest using a macro, fixUSzip5, to fix so that all
of your zip codes are text. The column should be formatted
as text so that future zip codes will be entered as text.
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

When finished you will have all text entries which is good
for 5 digit and 9 digit zip codes and will be left aligned
by default, and you can put any country's "zip code" in
the field without any problem.

Using a macro beats adding the extra "helper column",
and eliminates the problem with some zip codes being
numeric instead of text.

Numeric zip codes will interfere with sorting as you have
seen, and numeric zip codes will not work properly with
Mail Merge as you would lose a leading zero in many
of the zip codes.