Validation of Postal Code
 

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

Validation of Postal Code
 

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


http://groups.google.ca/group/micros...d254817?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.

Veronika wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html