Validation of Postal Code

[Veronika]

Hi;

is there a way to validate CND postal codes in a spreadsheet? If not how can
I format the column to accept only A9A9A9 type of data? (Upper case,
number,uppercase, etc.)
Thank you,

Veronika

 

[Debra Dalgleish]

You can use the custom data validation formula that J.E. McGimpsey shows
in this message:


http://groups.google.ca/group/microsoft.public.excel.misc/msg/4e93ebdb2d254817?hl=en&

If you want to check for upper case, you can add the Exact function to
the formula. However, the formula would then be too long for the data
validation formula box:


=AND(EXACT(A1,UPPER(A1)),(LEFT(A1)>="A")*(LEFT(A1)<="Z")*(MID(A1,2,1)>="0")*(MID(A1,2,1)<="9")*(

MID(A1,3,1)>="A")*(MID(A1,3,1)<="Z")*(MID(A1,4,1)>="0")*(MID(A1,4,1)<="9

")*(MID(A1,5,1)>="A")*(MID(A1,5,1)<="Z")*(MID(A1,6,1)>="0")*(MID(A1,6,1)
<="9")*(LEN(A1)=6))

You could enter the formula in an adjacent cell, and in the cell where
the postal code is entered, use data validation to check that the
formula result is true.