Zip Code Sort Errors

Topic

New Reply

I have a sheet with names and address, including a Zip column. There are about 426 names in it. When I click in a cell in the zip column, and click the ascending sort button, the sheet is sorted by zip, but into TWO GROUPS. The first set is about 38 rows, and goes from 28203 to 28105 (why 28105 isn’t before 28203, I don’t know either). Then it starts over at 28203 and sorts the rest of the rows in ascending order. All of the cells for Zip are “general”. I can’t figure out what’s different about these 38 rows to make them sort above the other 380 or so rows. This screws up label printing in Word for mass mailing. Any ideas on what might be causing this??

thx.

-Rich Belthoff

Reply | Quote

Replies

Zip codes can start with a zero and thus are often entered as text by preceeding the number with an apostrophe (‘) which keeps the leading zero from being supressed. This would cause the sort order to change. The ‘ doesn’t display in the cell, but if you highlight the cell it will appear in the edit line. Perhaps this is your problem.

Reply | Quote

Why would that change the sort order?

Reply | Quote

If some are entered as text and some as numbers then the text entries sort after the numbers — at least they do when I try it. That changes the apparent sort order.

Reply | Quote

Yes, if some are numbers and some are text, then the sort order would not be the way you wanted. I read your message to say that all were entered with the single quote.

Reply | Quote

Thanks, Chuck, but that’s not it. All of the zip codes begin with “28”. I fixed it by re-typing and then copy/pasting groups of zip codes. There were no leading ‘ characters or blanks as far as I could tell, and I tried formatting them all as “General”, but I guess some were entered as numbers and some as text. That’s all I can think of.

-Rich.

Reply | Quote

If the values are stored as text, the following procedure will convert to numeric values. In a blank cell enter the value 1. Copy it. Then select the entire range of zip codes and Paste Special and select Multiply. Then sort and see if it makes a difference. You can apply formatting if you need to show leading zeros.

Andrew C

Reply | Quote

I think maybe some were stored as text, and converting all cells to “General” format doesn’t seem to fix that. I fixed it by re-typing and then copy/pasting groups of zip codes.

Thx.

-Rich.

Reply | Quote

Sorry I’m late to the party. What you could have done is convert them to values =value() then format those as Special, Zip Codes. Excel will then sort them correctly.

(I get zip code censuses a lot, and the formats are so often inconsistent I use this formula to convert them: =VALUE(LEFT(TEXT(,”00000″),5).) HTH,

Reply | Quote

It sounds like the ZIP codes are entered as text and have a variable number of blanks in front of the first digit. To get it to sort correctly, you can do one of several things. You could convert them to numbers and use a format that displays five digits with leading zeros. Or you could enter them with a single quote in front of the number (that tells Excel to treat the number as text) and the five digit number with leading zeros but no leading blanks.

Reply | Quote

If you select the whole range, either Format/Cells or the Text to Columns Wizard will format the lot as text, easier than entering with a leading ‘. The trim function removes leading (and trailing) blanks.

Reply | Quote