Code For Excel And Outlook Blog

Formatting Zip+4 Codes

If you have a column of zip+4, this macro will remove the suffix. Simply highlight the cells in question. It will skip any zip codes that don’t have the suffix. It uses the Left$ string function which is more efficient than the standard Left() and always returns the first 5 characters in the cell.

1
2
3
4
5
6
7
8
9
10
11
12

Sub Convert_ZipCode_Fix_ZIP_plus4()
Dim cell As Excel.Range
 
With WorksheetFunction
    For Each cell In Selection
    ' cell = .Trim(cell) ' optional
      If Len(cell) > 5 Then
        cell = Left$(cell, Len(cell) - (Len(cell) - 5))
      End If
    Next cell
End With
End Sub

Enjoy,
JP