Formatting Zip+4 Codes

January 25, 2008 – 1:49 pm by JP

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.


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

If you enjoyed this page, share it!