Validate format of values
 

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?
--
maryj

Validate format of values
 

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

On Mon, 19 Mar 2007 12:38:20 -0700, maryj
wrote:

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?

Validate format of values
 

Thanks! Perfect!
--
maryj


"Per Erik Midtrød" wrote:

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

On Mon, 19 Mar 2007 12:38:20 -0700, maryj
wrote:

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?