Zip Code Formatting
 

Hi All,

I receive a monthly excel spreadsheet that contains zip codes in
different formats. I'm assigning sales reps to each zip code. I ran
into difficulty because this file contains both US zips (need first 5
#s of the string) and Canadian zips, which is a mix of text and numbers
but always begins with a letter (e.g. "V5T 4T5").

I'm not sure how to differentiate between #s and letters.

Could someone help me:
1. Format the cells that begin with a number to be the first 5
characters only.
2. Don't edit the cells that begin with letters
3. Assign cells that begin with letters as "Canada" in Cells(x,9)

My code is as follows:

Sub RepAssignment ()
x = 2
y = 9

Do While Cells(x,8).Value < ""
If (Cells(x,8).Value 0) and (Cells(x,8).Value <=999) Then Cells(x,9) =
"LH"
If (Cells(x,8).Value 1000) and (Cells(x,8).Value <=2799) Then
Cells(x,9) = "LS"
...etc...
If (Cells(x,8).Value =99500) and (Cells(x,8).Value <= 99999) Then
Cells(x,9) = "H"
x = x + 1
Loop

End Sub


Thanks for your help in advance,
Christine


--
weeshie73
------------------------------------------------------------------------
weeshie73's Profile: http://www.excelforum.com/member.php...o&userid=24792
View this thread: http://www.excelforum.com/showthread...hreadid=536473

Zip Code Formatting
 

if isnumeric(left(cells(x,8).value,1)) then
' US Zip

else
' Canadian Zip

end if

--
Regards,
Tom Ogilvy


"weeshie73" wrote:


Hi All,

I receive a monthly excel spreadsheet that contains zip codes in
different formats. I'm assigning sales reps to each zip code. I ran
into difficulty because this file contains both US zips (need first 5
#s of the string) and Canadian zips, which is a mix of text and numbers
but always begins with a letter (e.g. "V5T 4T5").

I'm not sure how to differentiate between #s and letters.

Could someone help me:
1. Format the cells that begin with a number to be the first 5
characters only.
2. Don't edit the cells that begin with letters
3. Assign cells that begin with letters as "Canada" in Cells(x,9)

My code is as follows:

Sub RepAssignment ()
x = 2
y = 9

Do While Cells(x,8).Value < ""
If (Cells(x,8).Value 0) and (Cells(x,8).Value <=999) Then Cells(x,9) =
"LH"
If (Cells(x,8).Value 1000) and (Cells(x,8).Value <=2799) Then
Cells(x,9) = "LS"
...etc...
If (Cells(x,8).Value =99500) and (Cells(x,8).Value <= 99999) Then
Cells(x,9) = "H"
x = x + 1
Loop

End Sub


Thanks for your help in advance,
Christine


--
weeshie73
------------------------------------------------------------------------
weeshie73's Profile: http://www.excelforum.com/member.php...o&userid=24792
View this thread: http://www.excelforum.com/showthread...hreadid=536473