0

I am trying to check our state and country fields to make sure they are being entered correctly from an integration and get a notification if a state or country is incorrect. However, the Contains function does not seem to want to work with the : to separate multiples. I am getting strange results. For example with the following short version if I put anything, including the specified names, into the field I get a result of "unknown". IF(CONTAINS(MailingCountry, "AUSTRALIA:BRITAIN:CANADA"), "KNOWN", "UNKNOWN")

but if I switch the MailingCountry to the end of the formula then it kinda works except even partial pieces of the names get set as "known" such as US and AIN. And that won't work.

IF(CONTAINS("AUSTRALIA:BRITAIN:CANADA", MailingCountry), "KNOWN", "UNKNOWN")

I've tried moving things around and just can't figure out why it doesn't work at all one way and then is too lenient the other way and matching things that aren't supposed to be matched. I don't want to use nested if then statements for each state/country because not only would that be tedious it would also go over the allowed formula length. Any insight would be greatly appreciated.

Updating to say I have tried it with Upper to check for case. For some reason the version that somewhat works also "matches" letters that are just part of the name instead of the whole thing.

2
  • As Caspar mentions in his answer, the CONTAINS function is case-sensitive, see documentation. Commented Aug 26, 2022 at 14:30
  • Oh and I noticed that someone voted this question down. My question to that person is this: How would you, in your infinite wisdom, have asked it better? The asker provided examples of what they tried and a clear explanation of their goal. What more could they have done?? Commented Aug 27, 2022 at 0:33

2 Answers 2

1

Because the country names aren't the same length (which is why one prefers to use countryCodes, but that is another story), you need to do:

IF(CONTAINS( '|AUSTRALIA|:|BRITAIN|:|CANADA|', 
  '|' & UPPER(MailingCountry) & '|'), 'Known','Unknown')

this avoids partial matches as | is never part of a country name that a user would input.

if you are worried about the |, you could use any UTF-8 character like 𒀍 (which is cuneiform and really unlikely to appear in a user's form)

6
  • Oh very nice. Clever. Commented Aug 26, 2022 at 19:44
  • Thank you so much! That seems to have solved the issue. I tried to upvote it but it said I don't have enough reputation. If you don't mind can explain how the | symbols helped with the partial match issue when it was even matching letters in the middle of the country names previously? And why they were needed around the Upper(MailingCountry) part? Thanks Commented Aug 27, 2022 at 19:52
  • 1
    Because @cropredy has surrounded the search term (country) with a known delimiter, this means an unknown country like ITA becomes |ITA| - before it gets passed to the contains function. Since there is no |ITA| present, it's marked as unkown. BRITAIN becomes |BRITAIN| - which is present, and hence marked as known. Cool eh! Commented Aug 27, 2022 at 21:24
  • @AmandaHowell you can mark the answer as accepted even with your current reputation points.
    – cropredy
    Commented Aug 28, 2022 at 5:40
  • So the | symbol does what I thought the quotes did and tells it to only check for the entire word and not just letters in the word? Commented Aug 29, 2022 at 11:39
1

I think that the main issue you are having is case-sensitivity.

Try uppercasing the MailingCountry field, because only exact matches will work for this function.

IF(CONTAINS( "AUSTRALIA:BRITAIN:CANADA", UPPER(MailingCountry)), "Known","Unknown")

Also, it's good to apply an UPPER or LOWER function in this case, because it will then catch any examples such as: Australia, australia, caNada etc.

2
  • Yeah, I've tried that. Copy and Pasted your answer just to see if it was somehow different from the one I tried and it still gives me "Known" for things like "US" and "RAL". Pretty much if the letters are in the Countries name then it says it's known which makes no sense. Commented Aug 26, 2022 at 16:24
  • Check out cropredy's solution - it will eliminate any "US" and "RAL" issues. Commented Aug 26, 2022 at 19:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .