Adding Leading Zeroes to ZIP Codes
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 MakeZIPTxt()
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 cells are formatted as text and leading zeroes are added back to those cells
where it is necessary.
