Adding Canadian Postal Codes to Excel 07

Hi,

it's not pretty, but it can be done with data validation. You basically need to check if

1. the string entered is exactly 7 characters
=LEN(A1)=7
2. the leftmost character is an upper case character, which is a code between 65 ("A") and 90 ("Z")
=AND(CODE(LEFT(A2,1))>=65,CODE(LEFT(A2,1))<=90)
3. the second character is a number
=ISNUMBER(VALUE(MID(A1,2,1)))
4. the third character is an upper case character
=AND(CODE(MID(A1,3,1))>=65,CODE(MID(A1,3,1))<=90)
5. the fourth character is a blank, i.e. Code is 32
=CODE(MID(A1,4,1))=32
6. the fifth character is a number
=ISNUMBER(VALUE(MID(A1,5,1)))
7. the sixth character is an upper case character
=AND(CODE(MID(A1,6,1))>=65,CODE(MID(A1,6,1))<=90)
8. the seventh character is a number
=ISNUMBER(VALUE(MID(A1,7,1)))

Putting it all together into one formula makes:

=AND(LEN(A1)=7,AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<90),ISNUMBER(VALUE(MID(A1,2,1))),AND(CODE(MID(A1,3,1))>64,CODE(MID(A1,3,1))<90),CODE(MID(A1,4,1))=32,ISNUMBER(VALUE(MID(A1,5,1))),AND(CODE(MID(A1,6,1))>64,CODE(MID(A1,6,1))<90),ISNUMBER(VALUE(MID(A1,7,1))))

(As I said, it's not pretty) :-)

This is the formula we would need to use for data validation. Unfortunately, it is more than 255 characters long, so Excel 2003 won't accept it. So we have to shorten it a bit. Instead of validating each number in the above, we try and hit three numbers in one go, adding them up and checking if the result is a number. If not, one of the characters must be invalid. If it is a number, then all three characters must be numbers, too.

=ISNUMBER(VALUE(MID(A1,2,1))+VALUE(MID(A1,5,1))+VALUE(MID(A1,7,1)))

After replacing the ISNUMBER bits with the new version, the formula looks like this:

'=AND(LEN(A3)=7,AND(CODE(LEFT(A3,1))>=65,CODE(LEFT(A3,1))<=90),AND(CODE(MID(A3,3,1))>=65,CODE(MID(A3,3,1))<=90),CODE(MID(A3,4,1))=32,AND(CODE(MID(A3,6,1))>=65,CODE(MID(A3,6,1))<=90),ISNUMBER(VALUE(MID(A1,2,1))+VALUE(MID(A1,5,1))+VALUE(MID(A1,7,1))))

Now, copy this formula from this forum, then go and click cell A1 in your spreadsheet.

Click Data - Validation
Select Custom, click the formula box and paste the formula. Click the other tabs to enter helpful messages for your user. Click OK.

Now you can test it in cell A1. If it behaves the way you want it, copy A1, then Paste Special - Validation onto all cells where you want this check applied.

hth

How about using Data|Validation >> Custom formula:

=AND(EXACT(LEFT(A1)&MID(A1,3,1)&" "&MID(A1,6,1),UPPER(LEFT(A1)&MID(A1,3,1)&" "&MID(A1,6,1))),ISNUMBER(MID(A1,2,1)+MID(A1,5,1)+RIGHT(A1)))

Assuming you are validating cell A1.