Adjust Zip code to 9 Char fmt.

[Jeffery B Paarsa]

Hello,

I have a sheet with Column name PtZip and it has Zip code in them... Some of
these zip code are old format of 5 char. and I would like to convert them to
9 Char format. Not sure if there is a way by reading the related Address
Column can convert 5 Char zip format to 9 char format but at least pad the
zip with four zero on the right. so my formating does not show as
00009-2777.

 

[Rick Rothstein]

Give this macro a try...

Sub Zip9()
Dim X As Long
Dim LastRow As Long
Const ColLetter As String = "A"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row
For X = 1 To LastRow
If Len(.Cells(X, ColLetter).Value) = 5 Then
.Cells(X, ColLetter).Value = .Cells(X, ColLetter).Value & "-0000"
End If
Next
End With
End Sub

 

[Jeffery B Paarsa]

Hi,

Thanks for the response, but it seems this is for the entire page... I want
to run this only for PtZip column not the entire page.

 

[Rick Rothstein]

No, it only operates on the worksheet whose name is assigned to the
SheetName constant in the column whose column letter designation is assigned
to the ColLetter constant in the two Const statements at the beginning of
the code. Just change these values to your sheet name and column letter and
it should work only on that column on that worksheet.

 

[macropod]

Hi Jeffery,

Just be aware that those extra 0s will become part of the data and will be included in any CSV extracts and mailmerges ...

A better solution might be to use an event-driven macro to change the cell formatting, depending on the length of the zip code
entered.

 

[Jeffery B Paarsa]

Hi,

I ran the following code:
Sub Zip9()
Dim X As Long
Dim LastRow As Long
Const ColLetter As String = "Ptzip"
Const SheetName As String = "PtTable"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row
For X = 1 To LastRow
If Len(.Cells(X, ColLetter).Value) = 5 Then
.Cells(X, ColLetter).Value = .Cells(X, ColLetter).Value & "-0000"
End If
Next
End With
End Sub

And I am getting Run-time error "13" type mismatch
on line:
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row

 

[Rick Rothstein]

Ptzip is not a column letter... the column letters are located in the "gray"
boxes at the top of the column (assuming you didn't turn their display
off)... I'm guessing Ptzip is your column header.

--
Rick (MVP - Excel)

Hi,

I ran the following code:
Sub Zip9()
Dim X As Long
Dim LastRow As Long
Const ColLetter As String = "Ptzip"
Const SheetName As String = "PtTable"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row
For X = 1 To LastRow
If Len(.Cells(X, ColLetter).Value) = 5 Then
.Cells(X, ColLetter).Value = .Cells(X, ColLetter).Value & "-0000"
End If
Next
End With
End Sub

And I am getting Run-time error "13" type mismatch
on line:
LastRow = .Cells(.Rows.Count, ColLetter).End(xlUp).Row

 

[Jeffery B Paarsa]

Thanks... Yes it did worked.
--
Jeff B Paarsa

Ptzip is not a column letter... the column letters are located in the "gray"
boxes at the top of the column (assuming you didn't turn their display
off)... I'm guessing Ptzip is your column header.

 

[Jeffery B Paarsa]

Hi,

I added the follwong ling to change the format of the cell... No error but
it does not work.. Before End If
.Cells(X, ColLetter).Format.Special ("Zip Code + 4")

 

[Ron Rosenfeld]

Hello,

I have a sheet with Column name PtZip and it has Zip code in them... Some of
these zip code are old format of 5 char. and I would like to convert them to
9 Char format. Not sure if there is a way by reading the related Address
Column can convert 5 Char zip format to 9 char format but at least pad the
zip with four zero on the right. so my formating does not show as
00009-2777.

You could use one of these custom formats:

To differentially display 5 and 9 digit zip codes:

[<=99999]00000;00000-0000

or to append "-0000" if the input is only a 5 digit zip code:

[<=99999]00000"-0000";00000-0000
--ron

 

[Jeffery B Paarsa]

Hi,

How can this custom formating be done programatically when I attach the 0000
to 5 char zip codes?

--
Jeff B Paarsa

Hello,

I have a sheet with Column name PtZip and it has Zip code in them... Some of
these zip code are old format of 5 char. and I would like to convert them to
9 Char format. Not sure if there is a way by reading the related Address
Column can convert 5 Char zip format to 9 char format but at least pad the
zip with four zero on the right. so my formating does not show as
00009-2777.

You could use one of these custom formats:

To differentially display 5 and 9 digit zip codes:

[<=99999]00000;00000-0000

or to append "-0000" if the input is only a 5 digit zip code:

[<=99999]00000"-0000";00000-0000
--ron

 

[Ron Rosenfeld]

Hi,

How can this custom formating be done programatically when I attach the 0000
to 5 char zip codes?

This line will custom format the entire column A:A to that specification:

Range("A1").EntireColumn.NumberFormat = _
"[<=99999]00000""-0000"";00000-0000"

For "a1" you can substitute a cell reference in the column where your zip codes
will be stored.
--ron