Macro 50: Truncate ZIP Codes to the Left Five
U.S. ZIP codes come in either 5 or 10 digits. Some systems output a 10-digit ZIP code, which, for the
purposes of a lot of Excel analysis, is too many. A common data standardization task is to truncate ZIP
codes to the left five digits. Many of us use formulas to do this, but if you are constantly cleaning up
your ZIP codes, the macro outlined in this section can help automate that task.
It’s important to note that although this macro solves a specific problem, the concept of truncating
data remains useful for many other types of data cleanup activities.
How it works
This macro uses the Left function to extract the left five characters of each ZIP code in the given range.
Sub Macro50()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: Extract out the left 5 characters.
If Not IsEmpty(MyCell) Then
MyCell = Left(MyCell, 5)
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as the macro enumerates
through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can’t undo the changes a
macro makes. Because we are actually changing data, we need to give ourselves the option
of saving the workbook before running the macro. This is what Step 2 does. Here, we call up
a message box that asks if we want to save the workbook first. It gives us three choices: Yes,
No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking
Cancel exits the procedure without running the macro. Clicking No runs the macro without
saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell.
5. After a cell is activated, Step 5 uses the IsEmpty function to make sure the cell is not empty.
We do this to save a little on performance by skipping the cell if there is nothing in it. We
then pass the cell’s value through Left function. The Left function allows you to extract
out the nth left-most characters in a string. In this scenario, we need the left five characters in
order to truncate the ZIP code to five digits.
6. Step 6 loops back to get the next cell. After all of the cells in the target range are activated,
the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
