You are here: Home > Excel > Formatting Zip+4 Codes

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.

plain text version
VBA:
  1. Sub Convert_ZipCode_Fix_ZIP_plus4()
  2. Dim cell As Excel.Range
  3.  
  4. With WorksheetFunction
  5.     For Each cell In Selection
  6.     ' cell = .Trim(cell) ' optional
  7.       If Len(cell)> 5 Then
  8.         cell = Left$(cell, Len(cell) - (Len(cell) - 5))
  9.       End If
  10.     Next cell
  11. End With
  12. End Sub

Enjoy,
JP


Share and Enjoy:
  • StumbleUpon
  • Technorati
  • Digg
  • Google
  • del.icio.us
  • MisterWong

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 5 views since January 25, 2008 – 1:49 pm.

Post a Comment

To post VBA code in your comment, use [VBA] tags, like this: [VBA]Code goes here[/VBA].





Subscribe without commenting

Keep Reading:

Browse Posts:


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

               Copyright © 2007,2008 Code For Excel And Outlook || Disclaimer