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:

 LEN(Country) <> 2, 
    "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.


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.


  • I will look into that. – Mike Chale Aug 8 '12 at 17:51
  • While this did not directly answer my question, it is definitely a more elegant solution. – Mike Chale Aug 9 '12 at 19:27

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.

  LEN(Country) <> 2, 
    "BB:BD:BE:BF:B <snip>)
  • 1
    I find it more natural to use && for AND and || for OR. I feel it makes it more readable: !ISBLANK(Country) && (LEN(Country) <> 2 || !CONTAINS("00:AD:AE:AF", Country)) – Daniel Hoechst Aug 8 '12 at 14:40
  • I didn't even realize that was an option! All of the examples I've ever seen just use AND & OR. Thank you! – Mike Chale Aug 8 '12 at 14:58
  • Isn't it redundant to check ISBLANK(Country) and LEN(Country) <> 2? A valid code is one where the length is 2, AND it's contained in the list of valid codes. – tomlogic Aug 27 '12 at 23:39
  • No, because this allows for blank (unknown) data. If it didn't have the IsBlank the field would essentially be required. This way, if the field is not blank, the data must be 2 characters long. – Mike Chale Aug 28 '12 at 1:28

This exists in the Formula's help documentation:

LEN(BillingCountry) = 1,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.