VBA to fix ZipCodes

**davidsons** · 04-27-2011, 04:07 PM

In my file I have a column with zipcodes. Some times the zipcodes are only 4 digits because the 0 in front was dropped. Is there code I can put in that would add the leading zero wehn it is four digits and format the column to text?

**tlafferty** · 04-27-2011, 04:30 PM

Assuming your zips are in Column A and that you will run the code after selecting the sheet to modify:

Sub FixZips()
Dim lngLastRow As Long
lngLastRow = ActiveSheet.UsedRange.Rows.Count

For Each c In Range("A1:A" & lngLastRow)
    If Len(c.Value) = 4 Then c.Value = "'0" & c.Value
Next

End Sub

**MarvinP** · 04-27-2011, 04:35 PM

Hi davidsons,

There is a custom format of zip codes in most versions of excel.
Or see if any of these links help.
http://office.microsoft.com/en-us/ex...010274823.aspx
http://excel.tips.net/T002793_Sorting_ZIP_Codes.html
http://www.ofzenandcomputing.com/zanswers/3552/

**davidsons** · 04-28-2011, 11:23 AM

I couldn't get it to work and tried to modify the code, but it is still not working. Any suggestions?

Columns("M:M").Select
Selection.NumberFormat = "@"

Dim lngLastRow As Long
lngLastRow = ActiveSheet.UsedRange.Rows.Count

For Each c In Range("M2:M" & lngLastRow)
    If Len(c.Value) = 4 Then c.Value = "'0" & c.Value
Next