G
Greegan
Again, thanks for all your help so far...
And thanks in advance for this one.
I have zip codes in Column I
I have the Country Code (CA for Canada, GB for England and such, and US for
....) in Column H
As you know some zip codes for the US can have a 5 or 9-digit format 90210
and 90210-1111 (for example although I know that is not a true code).
However some zip codes we will have appear in Column I will have only 4 or
even 3 digit zip code (latter example for PR) and we will use the Format
cell Special Zip Code.
For this fact we will not select Column I and Format Cell Special Zip Code+4
as the extended digits will only appear as zeroes...
So our only solution is to remove the last four digits on the Right hand
side of the 9-digit zipcodes (beit 30%, 50%, or 90% of the whole column).
Currently my solution is this:
Copy and Insert copied column into J from I (so i have two zip code columns)
Then I use =len(I1) and fill down.
I sort by descending order and find all the 9-digit zips
Text to Column Fixed length to 5 characters.
Then 8-digit zips are fixed to 4 characters and so on (each time cutting off
4 digits) - only done to 7 digits and left with 3 fixed (for PR zips).
Now the macro I have below was done by a co-worker of mine that seemed to
work but I have since found errors with the resulting zip codes.
If you can suggest something please do.
Keep in mind that I only want these fixed lengths IF "US" is in column H.
I'm actually looking for a macro for two scenarios:
Column I (zip codes) and Column H (Country)
and a second macro doing the same action but with the following changes
Column L (zip codes) and Column S (Country)
The macro below was meant to work with the former.
I'm looking for a simpler way to do this.
Sub SWO_ZipCods9()
'
' MY_ZipCods9 Macro
' Macro recorded 03/10/2004 by Scott Beattie
'
' Changes nine-character zip codes to five or less cutting off the four
digits to the right only if the country code column contains "US"
'
Cells.Select
Selection.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("I:I").Select
Selection.Copy
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Range("J1").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-2]<>""US"",RC[-1],INT(LEFT(RC[-1],5)))"
Range("J1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Selection.FillDown
Columns("J:J").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Selection.NumberFormat = "00000"
End Sub
And thanks in advance for this one.
I have zip codes in Column I
I have the Country Code (CA for Canada, GB for England and such, and US for
....) in Column H
As you know some zip codes for the US can have a 5 or 9-digit format 90210
and 90210-1111 (for example although I know that is not a true code).
However some zip codes we will have appear in Column I will have only 4 or
even 3 digit zip code (latter example for PR) and we will use the Format
cell Special Zip Code.
For this fact we will not select Column I and Format Cell Special Zip Code+4
as the extended digits will only appear as zeroes...
So our only solution is to remove the last four digits on the Right hand
side of the 9-digit zipcodes (beit 30%, 50%, or 90% of the whole column).
Currently my solution is this:
Copy and Insert copied column into J from I (so i have two zip code columns)
Then I use =len(I1) and fill down.
I sort by descending order and find all the 9-digit zips
Text to Column Fixed length to 5 characters.
Then 8-digit zips are fixed to 4 characters and so on (each time cutting off
4 digits) - only done to 7 digits and left with 3 fixed (for PR zips).
Now the macro I have below was done by a co-worker of mine that seemed to
work but I have since found errors with the resulting zip codes.
If you can suggest something please do.
Keep in mind that I only want these fixed lengths IF "US" is in column H.
I'm actually looking for a macro for two scenarios:
Column I (zip codes) and Column H (Country)
and a second macro doing the same action but with the following changes
Column L (zip codes) and Column S (Country)
The macro below was meant to work with the former.
I'm looking for a simpler way to do this.
Sub SWO_ZipCods9()
'
' MY_ZipCods9 Macro
' Macro recorded 03/10/2004 by Scott Beattie
'
' Changes nine-character zip codes to five or less cutting off the four
digits to the right only if the country code column contains "US"
'
Cells.Select
Selection.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("I:I").Select
Selection.Copy
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Range("J1").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-2]<>""US"",RC[-1],INT(LEFT(RC[-1],5)))"
Range("J1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Selection.FillDown
Columns("J:J").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Selection.NumberFormat = "00000"
End Sub