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.

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



Print This Post Print This Post  |  Email This Post Email This Post  |  Permalink  |  Subscribe to this feed Subscribe now!

Filed Under: Excel, VBA
Tags: , , ,

This post has 3 views since January 25, 2008 – 1:49 pm.

Post a Comment

Browse Posts:


« Formula for Date/Time Subtraction in Excel | Advanced Topics In Excel Training Class »