Zip Code Formatting

[weeshie73]

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

 

[Guest]

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

else
' Canadian Zip

end if