Formatting Zip+4 Codes
January 25, 2008 • JP • No Comments • Rate This Article
• Links 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
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
Rate This Article:
(No Ratings Yet)
Loading ...
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
(No Ratings Yet) Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
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 »
Previous Post: Formula for Date/Time Subtraction in Excel
Next Post: Advanced Topics In Excel Training Class
It works! Thanks muchly!