Sorting mixed 5- and 9-digit zip codes in OpenOffice.org Calc or Microsoft Excel

Every advance leads to benefits and to problems.

The 9-digit zip code is great for delivery accuracy. But they aren't required. So you have mixed 5-digits and 9-digits.

Now just try sorting your address info by zip code when there's mixed 5s and 9s. Here's what you get when you sort a standard address list by zip code.  It's like cattle and sheep, they don't mix.

What do you do?

There are three things you can do:
1 - Enforce a 9-digit zip. Everyone without four digits gets -0000 whether they want it or not.
2 - Put a ' in front of every zip code. It doesn't print but it forces the zip code to think of itself as text. (This also helps with not losing leading zeroes.)
3 - Split the column into two, so you have the zip in one column and the four-digit extension in the other. This is kind of like the forcing-9-digit solution.

The explanations follow but if you want to root around in an example spreadsheet, here's a spreadsheet with options 2 and 3.

Solution 1
Solution 1 is self-explanatory.

Solution 2
Just type a '   a regular apostrophe on the keyboard, to the left of the first character of every zip code. It doesn't show but it forces text format.

See? The ' is there in the entry field but it doesn't show in the spreadsheet cell.

Typing ' into thousands of cells takes a while. So you can search and replace. There might be a better way to do this but this at least doesn't suck.

Click in the Zip Code heading in your spreadsheet, then  choose Edit > Find and Replace. Click More Options and fill out the window as shown, searching for ^0, caret zero, and replacing with '0, apostrophe zero.

You'll need to do this once for 0, then for 1, then for 2, and so on. (I've tried to figure out a faster way, plus submit any suggestions.) 

Either replace one at a time if you're cautious, or go nuts and replace all. You might want to select the whole column of zip codes, too, and select the Current Selection Only checkbox.

When you're done, and when you sort that data, the zip codes sort correctly.

Solution 3

You can split your data into two cells with the LEFT and RIGHT functions. I'm throwing in IF too because sometimes you'll want the right-hand four digits (the extension) and sometimes you'll want 0000. (Or just leave it blank, whatever you want to do.)

This is what I want to achieve.

And this is how I get it. The formula for the first column, where I extract the first five digits, is simple.

The right-hand side is a little more complicated since you're dealing with variable-length zip codes. But basically you're saying if the zip code is just five digits, then create a new 4-digit extension, "-0000" (or just "0000" depending on how you want to deal with the dash). And then if it isn't just five digits, then you want to see the right-hand five digits of the zip code (including the dash) or the right-hand four digits (if you want to leave out the dash and put it in manually somehow).

Then you just drag down those formulas to all the zip code cells.

If you want to turn those columns into normal text, just copy them, choose Edit > Paste Special, choose to NOT paste formulas, and click OK.

The pasted results are nothing but numbers.

Now when you sort, you just need to be sure to do it by two levels, first by the main zip code, then by the extension. BE SURE that you set the Ascending or Descending the same for both.

And you get your results, sorted correctly.

Here's a spreadsheet with options 2 and 3.

Comments

[hawkse]

A variation on your third option is to use the handy "text to columns" option available under the "Data" menu.

Just select the column, choose "Data » Text to columns", select "-" as the field separator and press OK.

[Solveig]

Hi Hawkse,

Beautiful! I've been asked if this feature exists and I guess I didn't connect the menu item name with the functionality. (In Excel apparently it's under right-click.) Thanks!

Solveig

[Kevin Fries]

Your answer to the how to search and replace in option #2 is actually quite easy.

In the Search Box:
^([:digit:])

In the Replace Box:
'$0

This will replace all lines starting with a digit only with a quote and the digit found in the search

Keep up the good work
Kevin Fries

[Pablo Caceres]

Hi Solveig:

I found a big trouble using Calc. Look, i was managing a big database, it is heavy, more than 13MB. Well, i created other databases in the same file adding new sheets. After that, i tried to make some changes and for to do it easily, i went to Window > Divide, then i saved the file and all sheets disappeared!!!. Any suggestions???

[Solveig]

Hi Pablo,

It's a database based on a spreadsheet, and now when you open the spreadsheet all the sheets disappeared? Did the disappearance take place when you view the datase or the spreadsheet itself?

[David Cowell]

I use a relatively simple solution to the multi-length ZIP code problem.

Before I start entering data, I select the column (or the appropriate cells), right click, select Format > Numbers, slide down to Text, OK. It doesn't hurt to set Alignment at Left, either.

The more important benefits of this approach are that:

you don't have to give an artificial "-0000" that may have to be searched for when making a pre-sorted mailing

you reduce unusual keyboarding of data, thus reducing possible errors in data

Incidentally, this problem doesn't crop up in Canadian and British postal codes.

Thanks for a truly informative blog!

[Solveig]

Kevin, thanks for the regex for the s/r! Perfect.

"Your answer to the how to search and replace in option #2 is actually quite easy.

In the Search Box:
^([:digit:])

In the Replace Box:
'$0

This will replace all lines starting with a digit only with a quote and the digit found in the search."