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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to hide a given column based on the value in a particular cell? The easiest way to accomplish the task is to use a ...
Discover MoreNeed to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good ...
Discover MoreWhen you create workbooks for others to use, you might want to make sure that they can't change the formatting and paper ...
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.
Comments