Validate Canadian Postal codes in Excel

A

Aloze

Would like to validate columns containing Canadian Postal Code for accuracy.
Any idea how to program the validation tool?
 
F

Fred Smith

One way is with a long if statement:

=if(and(mid(a1,1,1)>="A",mid(a1,1,1)<="Z",mid(a1,2,1)>="0",mid(a1,2,1)<="9",...),true,false)

I'm sure there are better ways, but I'll have to leave it up to people more
expert than I to provide them. It would help the non-Canadian posters to
this group to let them know the format of Canadian postal codes is A9A 9A9.
 
Top