Validate format of values

[Guest]

Client is using WinXP/Office 2003.
He has cells with Canadian postal codes. The postal codes should be 6
characters starting with a letter and then alternating every other with a
number. For example, V2L3M4. He would like a formula to evaluate the cell to
determine if the postal code follows the correct syntax. Any ideas?

 

[Per Erik Midtrød]

Hello Maryj.

If your zip code is in A1 you could use this formula:
=AND(ISNUMBER(LEFT(A1))=FALSE;ISNUMBER(MID($A1;2;1)*1);ISNUMBER(MID($A1;3;1)*1)=FALSE;ISNUMBER(MID($A1;4;1)*1);ISNUMBER(MID($A1;5;1)*1)=FALSE;ISNUMBER(MID($A1;6;1)*1);LEN(A1=6)
)

It checks each digit to determine whether it is text or number.

Per Erik

 

[Guest]

Thanks! Perfect!
--
maryj

Hello Maryj.

If your zip code is in A1 you could use this formula:
=AND(ISNUMBER(LEFT(A1))=FALSE;ISNUMBER(MID($A1;2;1)*1);ISNUMBER(MID($A1;3;1)*1)=FALSE;ISNUMBER(MID($A1;4;1)*1);ISNUMBER(MID($A1;5;1)*1)=FALSE;ISNUMBER(MID($A1;6;1)*1);LEN(A1=6)
)

It checks each digit to determine whether it is text or number.

Per Erik