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: Adding Leading Zeroes to ZIP Codes.
by Allen Wyatt
(last updated March 28, 2015)
When you import ZIP Codes from a text file into an Excel workbook, it is not uncommon for Excel to translate the values as numbers rather than as ZIP Codes. This results in leading zeroes being dropped from the ZIP Codes, which can obviously cause problems later using the data for its intended purpose.
One solution, of course, is to simply change the display format used for ZIP Code cells. This may work for the display, but the underlying data is still missing the leading zeroes. A better solution is to use a macro that goes through and adds leading zeroes to the information in a cell. The following macro does just that:
Sub MakeZIPText() Dim ThisCell As Range Application.ScreenUpdating = False 'Make sure format is text Selection.NumberFormat = "@" For Each ThisCell In Selection 'Strip the leading apostrophe, if any If Left(ThisCell, 1) = "'" Then ThisCell = Mid(ThisCell, 2, 99) End If 'It's a 5-digit ZIP Code If Len(ThisCell) <= 5 Then ThisCell = "'" & Right("00000" & ThisCell, 5) Else ThisCell = "'" & Right("00000" & ThisCell, 10) End If Next ThisCell Application.ScreenUpdating = True End Sub
To use the macro, simply select the range of cells containing the ZIP Codes, then run the macro. The macro actually changes the cell contents—no longer will the cells contain numeric values (the cause of the original problem), but they will contain text values. This allows the leading zeroes to appear at the beginning of the ZIP Codes.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2598) 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: Adding Leading Zeroes to 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!
If you need to consolidate a single column of data into multiple columns of data, you'll love this macro. It provides a way ...Discover More
Need a way, in a macro, to convert binary numbers into their decimal equivalents? There are two ways you can get the desired ...Discover More
Ever notice that if someone types in all CAPS, it appears they are shouting? If your worksheets include lots of text, you may ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.