Validating country codes

Question

Our enterprise data systems maintain a list of 2-character country codes that I want to enforce inside Salesforce. I'm having trouble writing a validation rule that successfully does this.

The rule on Lead.Country is:

AND( 
 !ISBLANK(Country), 
 LEN(Country) <> 2, 
 NOT(CONTAINS("00:AD:AE:AF:AG:AI:AL:AM:AN:AO:AQ:AR:AS:AT:AU:AW:AX:AZ:BA:"&
    "BB:BD:BE:BF:BG:BH:BI:BJ:BL:BM:BN:BO:BR:BS:BT:BV:BW:BY:BZ:"&
    "CA:CC:CD:CF:CG:CH:CI:CK:CL:CM:CN:CO:CR:CU:CV:CX:CY:CZ:"&
    "DE:DJ:DK:DM:DO:DZ:EC:EE:EG:EH:ER:ES:ET:FI:FJ:FK:FM:FO:FR:"&
    "GA:GB:GD:GE:GF:GH:GI:GL:GM:GN:GP:GQ:GR:GS:GT:GU:GW:GY:HK:HM:HN:"& 
    "HR:HT:HU:ID:IE:IL:IM:IN:IO:IQ:IR:IS:IT:JE:JM:JO:JP:KE:KG:KH:KI:KM:KN:KP:"& 
    "KR:KW:KY:KZ:LA:LB:LC:LI:LK:LR:LS:LT:LU:LV:LY:MA:MC:MD:ME:MF:MG:MH:MK:ML:"& 
    "MM:MN:MO:MP:MQ:MR:MS:MT:MU:MV:MW:MX:MY:MZ:NA:NC:NE:NF:NG:NI:NL:NO:NP:NR:"& 
    "NU:NZ:OM:PA:PE:PF:PG:PH:PK:PL:PM:PN:PR:PS:PT:PW:PY:QA:RE:RO:RS:RU:RW:SA:"& 
    "SB:SC:SD:SE:SG:SH:SI:SJ:SK:SL:SM:SN:SO:SR:ST:SV:SY:SZ:TC:TD:TF:TG:TH:TJ:"& 
    "TK:TL:TM:TN:TO:TR:TT:TV:TW:TZ:UA:UG:UM:US:UY:UZ:VA:VC:VE:VG:VI:VN:VU:WF:"& 
    "WS:XK:YE:YT:YU:ZA:ZM:ZW", Country)) 
  )

Here are some sample results:

• Fails validation
  • "1"
  • "ABC"
• Passes validation
  • "A"
  • "AB"

It seems that since every letter of the alphabet is in the Contains block and single or two-character combination passes.

Answer 1

Why not trying using a VLookup. you can store all of your values in an object and then use the VLOOKUP() formula to see if it exists in the list. That way you avoid running into the size limit on formula fields and you can more easily maintain the list of valid country codes.

https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions.htm&language=en#VLOOKUP

Answer 2

The problem turned out to be in the AND clause. I re-examined the Salesforce example for US states and realized I need to OR together the LEN and CONTAINS functions.

AND( 
 !ISBLANK(Country), 
 OR(
  LEN(Country) <> 2, 
  NOT(CONTAINS("00:AD:AE:AF:AG:AI:AL:AM:AN:AO:AQ:AR:AS:AT:AU:AW:AX:AZ:BA:"&
    "BB:BD:BE:BF:B <snip>)
    )
 )

Answer 3

This exists in the Formula's help documentation:

OR(
LEN(BillingCountry) = 1,
NOT(
CONTAINS(
"AF:AX:AL:DZ:AS:AD:AO:AI:AQ:AG:AR:AM:" &
"AW:AU:AZ:BS:BH:BD:BB:BY:BE:BZ:BJ:BM:BT:BO:" &
"BA:BW:BV:BR:IO:BN:BG:BF:BI:KH:CM:CA:CV:KY:" &
"CF:TD:CL:CN:CX:CC:CO:KM:CG:CD:CK:CR:CI:HR:" &
"CU:CY:CZ:DK:DJ:DM:DO:EC:EG:SV:GQ:ER:EE:ET:FK:" &
"FO:FJ:FI:FR:GF:PF:TF:GA:GM:GE:DE:GH:GI:GR:GL:" &
"GD:GP:GU:GT:GG:GN:GW:GY:HT:HM:VA:HN:HK:HU:" &
"IS:IN:ID:IR:IQ:IE:IM:IL:IT:JM:JP:JE:JO:KZ:KE:KI:" &
"KP:KR:KW:KG:LA:LV:LB:LS:LR:LY:LI:LT:LU:MO:MK:" &
"MG:MW:MY:MV:ML:MT:MH:MQ:MR:MU:YT:MX:FM:MD:MC:" &
"MC:MN:ME:MS:MA:MZ:MM:MA:NR:NP:NL:AN:NC:NZ:NI:" &
"NE:NG:NU:NF:MP:NO:OM:PK:PW:PS:PA:PG:PY:PE:PH:" &
"PN:PL:PT:PR:QA:RE:RO:RU:RW:SH:KN:LC:PM:VC:WS:" &
"SM:ST:SA:SN:RS:SC:SL:SG:SK:SI:SB:SO:ZA:GS:ES:" &
"LK:SD:SR:SJ:SZ:SE:CH:SY:TW:TJ:TZ:TH:TL:TG:TK:" &
"TO:TT:TN:TR:TM:TC:TV:UG:UA:AE:GB:US:UM:UY:UZ:" &
"VU:VE:VN:VG:VI:WF:EH:YE:ZM:ZW",
BillingCountry)))