How do you sort 9 digit zip codes in Excel?

[Guest]

Nine digit zip codes do not sort properly in Excel even when the cell is
formatted as "Special". I use Excel to sort for mailing labels and cannot
use 9 digit zipcodes because of the error. Help!

 

[Vasant Nanavati]

Make sure the cells containing the zip codes are formatted as text.

 

[Tom Ogilvy]

in an adjacent column put in a formula like (assume the zip is in C1)

=if(len(C1) = 5,text(C1,"00000")& "-0000",Text(C1,"00000-0000"))

Drag fill down the column, then sort on that column.

 

[Guest]

Thank You!!!!!!!! This worked beautifully!

Martha

 

[Ron Rosenfeld]

On Sat, 21 May 2005 08:57:11 -0700, "Martha Parsons" <Martha

Nine digit zip codes do not sort properly in Excel even when the cell is
formatted as "Special". I use Excel to sort for mailing labels and cannot
use 9 digit zipcodes because of the error. Help!

Can you give an example of improperly sorted 9 digit zip codes? Although I
think I know what you mean, I was not able to replicate a sorting error.


--ron

 

[David McRitchie]

Martha (the OP), indicated she had the answer at the same time
you replied, evidently just new entries are the problem.

If there were a number of old entries you would want to convert
them to text, and I have a macro for that.
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

Tom, a zip code of 7234 formatted as 00000 to show 07234
would test as a length of 4 =LEN(A1)