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.

Attribution to: Mike Chale

Possible Suggestion/Solution #1

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>)
    )
 )

Attribution to: Mike Chale

Possible Suggestion/Solution #2

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

Attribution to: John De Santiago

Possible Suggestion/Solution #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)))

Attribution to: Amber Neill Boaz
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/230