Excel doesn't sort zip codes properly

[nrwayne]

I have a sizable worksheet (6,000 rows by 10 columns), which includes
names, addresses, and zip codes. When I try to sort on zip codes, the
worksheet sorts in two sections vertically, in ascending order. I
believe that is because some of the zip codes are formatted as values
and others are formatted as text. Even so, I have tried to format the
zip code column as text and it has not helped. So I'm wondering what I
need to change in the zip code cell formatting to make the proper sort
possible. Many thanks for suggestions.

 

[Jason Morin]

Use this in a new column to convert all zip codes to
text, and then sort on this new column:

=TEXT(IF(ISERROR(FIND("-",A1)),A1&"-0000",A1),"00000-
0000")

If the zip codes are all 5 digits (ie not in ZIP+4
format), use:

=TEXT(A1,"00000")

HTH
Jason
Atlanta, GA

 

[Guest]

I think you'd want to sort on number wouldn't you? Delete the column header,
highlight the entire column change the value to Number, then rename the
column header to Zip or whatever. I think that should work.

 

[nrwayne]

Brilliant and most helpful suggestion. The zip codes were all 5 digit,
so I use your option #2, then converted to values using Copy & Paste
Special. The sort now works fine. Many thanks.

 

[nrwayne]

Thanks, Micah. I followed Jason's suggestion and the sort works
perfectly now.