Debugging Excel ZIP Codes

(This article comes by way of one of our clients, Louis "Skip" Sander, of USSRankin.org)

If you’ve worked with ZIP codes in Excel, you may have had mysterious and frustrating problems with sorting, displaying, printing or mapping the ZIP codes. The problems are subtle and hard to detect, so even if you haven’t seen them, they could be adversely affecting your work. This article describes the problems, discusses their cause, and shows how to correct and avoid them. 

The Problems

One common and potentially serious problem is that after sorting, ZIP codes sometimes appear in two groups—one sorted from 00000 to 99999, followed by a second group, similarly sorted. In a long list, it’s easy for the user to be totally unaware of the improper sorting, since the list looks fine at both ends. Only careful observation will reveal that there are really two sorted groups. 

Another problem is that sometimes Excel drops the leading zeroes from ZIP codes in New England, New Jersey, Puerto Rico, and the Virgin Islands. 

Thirdly, it often happens that some of the ZIP codes in a list appear right-aligned in their cells, while others appear left-aligned; sometimes the right-aligned ZIP codes are flush with the right edge of the cell, and sometimes they are not quite flush. This is mostly a matter of aesthetics, but it can be a symptom of the more serious problems described above. 

The Causes

All these problems arise from improper or non-uniform formatting of the ZIP codes, and they usually cannot be corrected by merely reformatting cells. They are particularly common when ZIP codes are imported from other applications or pasted from other worksheets, or when someone has formatted cells manually. You don’t need to understand the causes of the problems, but looking into them should convince you of the benefits of the solution offered here. 

The Solution

Fortunately, there is a way to fix all the formatting all at once. It involves creating a new column for the ZIP codes, then moving the existing ZIP codes there in a manner that gives them uniform formatting.

Here’s how to do it:

Insert a blank column to the left of the column of mixed format ZIP codes. We’ll call the blank column the “new” one, and the column of ZIP codes the “old” one.

If the old column has a heading, copy it into the new column.

Select the new column and format it by using Format | Cells. Click the Number tab, then choose Special, then Zip code.

In the new column, select the cell adjacent to the first ZIP code in the old column.

Insert the following formula: =VALUE(cell address), where cell address is the cell address of the first ZIP code in the old column. That ZIP code should now appear in the active cell.

Copy the formula down the new column so it appears next to every ZIP code in the old one. The new column should now be filled with uniform, well-behaved ZIP codes, each of them the same as its possibly misbehaving partner to the right. 

At this point the new column contains formulas, not values, so a few more steps are needed: 

Select the new column and Copy it to the clipboard. 

Without deselecting the new column, choose Edit | Paste Special. Click the Values radio button, then click OK.

The new column should now contain five-digit numbers in Excel’s Zip Codes format. Leading zeroes should not be stripped, and sorting should work properly. If everything appears to be right, select the old column and delete it. Your worksheet will be exactly as it was before, except that the ZIP codes are now uniformly formatted. Save your workbook, and go on about your business. 

Comments are closed