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

[Tom Ogilvy]

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
>
>