Formatting Zip+4 Codes

January 25, 2008JPNo CommentsRate This ArticlenewLinks to this article


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

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 if you want to learn more about VBA. Keep Reading »

↑ Scroll to top
Previous Post:

Next Post:

1 Response(s) to Formatting Zip+4 Codes ↓

  1. Alex Kay says:

    It works! Thanks muchly!

Speak Your Mind

Tell us what you're thinking...

Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].




Site last updated September 2, 2010 @ 7:03 pm