How to check a zipcode

[hans]

I want to check if a zipcode is correct.
In the netherlands the first 4 characters of a zipcode are numbers
the next to characters are characters.
How can i test if this is the case.
I want to do this in vba and use excel 2002

Greetings hans

 

[Nick]

Can you not just use validation? - go to data and choose
validation.

 

[Mike]

This will return true if the first four characters are
numerical, and the 5th and 6th are between A and Z (not
case sensitive).

IsValidZip = Isnumeric(Left(Zip,4)) AND IIf(Asc(UCase(Mid
(Zip, 5, 1))) > 64 And Asc(UCase(Mid(Zip, 5, 1)) < 91),
True, False) AND
IIf(Asc(UCase(Mid(Zip, 6, 1))) > 64 And Asc(UCase(Mid(Zip,
6, 1)) < 91), True, False)

 

[Mike]

Eeep. I should have also put a test to ensure the length
is 6 characters prior to evaluating the rest.

IsValidZip = False
If Len(Zip) = 6 Then
IsValidZip = {all that stuff below}
End If

 

[Mike Waldron]

if isnumber(mid(zipcode,1,4)) and istext(zipcode,5,2)then
check = "true"
else
check = "false"
end if

The mid function parses the zip code while isnumber and
istext check the appropriate part of the zipcode to see if
it is valid. You can find an explanation of the mid
function in the VB help.