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.

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


About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 1 Comment(s) on Formatting Zip+4 Codes:

  1. It works! Thanks muchly!

This article is closed to any future comments.
Excel School