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.
--
Jeff B Paarsa

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff 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.
--
Jeff B Paarsa


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa

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.

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa

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.

--
Cheers
macropod
[Microsoft MVP - Word]


"Jeffery B Paarsa" wrote in message ...
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.
--
Jeff 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

--
Jeff B Paarsa


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
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.

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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

--
Jeff B Paarsa


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa

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


"Rick Rothstein" wrote:

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" wrote in message
...
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

--
Jeff B Paarsa


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff 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")

--
Jeff B Paarsa


"Rick Rothstein" wrote:

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" wrote in message
...
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

--
Jeff B Paarsa


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jeffery B Paarsa" wrote in message
...
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.
--
Jeff B Paarsa

On Wed, 27 May 2009 20:18:01 -0700, Jeffery B Paarsa
wrote:

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

Hi,

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

--
Jeff B Paarsa


"Ron Rosenfeld" wrote:

On Wed, 27 May 2009 20:18:01 -0700, Jeffery B Paarsa
wrote:

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

On Thu, 28 May 2009 08:37:37 -0700, Jeffery B Paarsa
wrote:

Hi,

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

--
Jeff B Paarsa


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