Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Sorting ZIP Codes.
Written by Allen Wyatt (last updated December 23, 2023)
This tip applies to Excel 97, 2000, 2002, and 2003
ZIP Codes come in two flavors: five-digit codes and nine-digit codes. If you type a column of ZIP Codes into Excel—some five-digits and some nine—you may wonder why they don't sort correctly.
The reason is that Excel recognizes the five-digit codes as numeric entries and the nine-digit codes as text entries. (It is the dash in the middle of the nine-digit codes that makes Excel treat the entry as text.) The problem with sorting the numbers is that Excel treats numeric and text values differently when sorting.
The solution (without resorting to formulas and additional columns for sorting) is to make sure that you force Excel to treat all your entries as text. You can do this by formatting a column as text before putting in your first ZIP Code. Simply follow these steps:
Figure 1. The Number tab of the Format Cells dialog box.
This formats the cells as text, and you can then sort the ZIP Codes correctly. Don't be misled here, however: You must make the format change before you enter values in the cells. If you format the cells as text after you enter information, they still won't sort properly. You can do the following, however:
Once this process is done, you can sort the cells, as desired.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2793) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Sorting ZIP Codes.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
It's easy to automatically set the contents of one cell to be equal to another cell. But what if you want to copy the ...
Discover MoreWhile the implementation of custom formats in Excel is not terribly robust, you can still achieve some amazing results ...
Discover MoreEnter a fraction into Excel, and you may be surprised that the program reduces the faction to its simplest form. If you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments