Excel doesn't sort zip codes properly
 

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.

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


-----Original Message-----
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.

.

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.

" wrote:

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.

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.


Jason Morin wrote:
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


-----Original Message-----
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.

.

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