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

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

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