Adjust Zip code to 9 Char fmt.

J

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.
 
R

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
 
J

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.
 
R

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.
 
M

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.
 
J

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
 
R

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)


Jeffery B Paarsa said:
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
 
J

Jeffery B Paarsa

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


Rick Rothstein said:
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.
 
J

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")
 
R

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
 
J

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


Ron Rosenfeld said:
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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top