PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Adjust Zip code to 9 Char fmt.

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Adjust Zip code to 9 Char fmt.

 
 
Jeffery B Paarsa
Guest
Posts: n/a
 
      28th May 2009
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
 
Reply With Quote
 
 
 
 
Rick Rothstein
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
> 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
 
Reply With Quote
 
Jeffery B Paarsa
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
> news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
> > 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
>
>
 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
news:F05EB05B-E77F-450B-9EE1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
>> > 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
>>
>>
 
Reply With Quote
 
macropod
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
> 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
 
Reply With Quote
 
Jeffery B Paarsa
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
> news:F05EB05B-E77F-450B-9EE1-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>
 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
news:C4FBC557-FE6E-4322-A3D2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:F05EB05B-E77F-450B-9EE1-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>>
>>
 
Reply With Quote
 
Jeffery B Paarsa
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
> news:C4FBC557-FE6E-4322-A3D2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:F05EB05B-E77F-450B-9EE1-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>
 
Reply With Quote
 
Jeffery B Paarsa
Guest
Posts: n/a
 
      28th May 2009
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" <(E-Mail Removed)> wrote in message
> news:C4FBC557-FE6E-4322-A3D2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:F05EB05B-E77F-450B-9EE1-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:88FE722F-928B-452E-ABBC-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>
 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      28th May 2009
On Wed, 27 May 2009 20:18:01 -0700, Jeffery B Paarsa <(E-Mail Removed)>
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
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Send an email from Excel using Outlook | macro for deleting specific columns & rows. Help. Please. No Clue. »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to permanently delete char char char char style in MS Word? =?Utf-8?B?Q3Jpcw==?= Microsoft Word Document Management 6 12th Jun 2011 09:47 PM
Ever seen "Char Char Char Char Char Char1 Char Char Ch" style? =?Utf-8?B?TWFyaWx5bg==?= Microsoft Word Document Management 2 4th Oct 2006 04:47 PM
Formatting changed to Char Char Char Char... =?Utf-8?B?TWVsaXNzYQ==?= Microsoft Word Document Management 10 14th Apr 2006 07:32 AM
8500 cells with phone number(7 char.), wishing to add area code (10 char.) moparz@gmail.com Microsoft Excel Misc 6 10th Mar 2006 05:13 PM
error LNK2019: unresolved external symbol "public: static class ATL::CStringT<char,class StrTraitMFC<char,class ATL::ChTraitsCRT<char> > > danip Microsoft VC .NET 0 5th Dec 2005 10:01 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 05:13 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top