sorting ZIP codes

  • Thread starter Thread starter KD
  • Start date Start date
K

KD

I am trying to sort a list of zip codes which contains some
regular 5-digit codes and some ZIP+4 (9 digits). The +4 ZIP
codes come out at the end, rather than being sorted in with
the others. Does anyone know how I can do this?
 
KD,

Easiest way is a helper column with the formula

=LEFT(A1,5)

and then sort on that column.

You can delete that when you are done.

PC
 
Easiest way is a helper column with the formula

=LEFT(A1,5)

and then sort on that column.

You can delete that when you are done.
Click to expand...

Given the symptoms the OP mentions, either 5-digit zip codes are numbers
formatted with leading zeros while the 9-digit zip codes are text, or all are
numbers formatted with leading zeros. Using LEFT alone wouldn't help in the
former case, and using only the 5-digit zip code to sort could lead to anomalies
in adjacent 9-digit zip codes. The OP should use

=IF(ISNUMBER(A1),TEXT(A1*IF(LEN(A1)<=5,10000,1),"00000\-0000"),
LEFT(A1&"-0000",10))
 
Back
Top