Using Contains and !Contains to filter a field

[EEnglish2]

I have a field that contains a list of comma separated state abbreviations and alternates (e.g. AZ, CO, DC, LA, MD, MI, NV, NV-WH, NY, NY-ON, RI, TN, WA, WA-MS, WA-SK).  Not every record has the same set of abbreviations and they are not in any semblance of order.  I want to be able to filter on certain states without flagging the alternates.  E.G., I was trying to use a filter with Contains([State],"NY") but I want to make sure that I'm not getting records that contain only NY-ON without NY.

The desired result would include this logic:

State contains "NY" but not "NY-ON" = True

State contains "NY" and "NY-ON" = True

State contains  "NY-ON" but not "NY" = False

I tried adding a comma and using Contains([State],"NY,") but that would rule out any record where NY was the only entry or at the end of the list.

I'd appreciate any guidance.

[EEnglish2]

@OTrieger Wouldn't I still need to use something like the proposed RegEx to check for one or two of the conditions?  I can't check for just "ny" without getting results for "ny-on" unless I use RegEx, can I?

[OTrieger]

@EEnglish2  Yes you can use Regex, but not have to. You can set an IF statement with the needed conditions to give you false or true. So regex can be useful however there is not a must using it it. You can use a simple Contains function
Such as Contains " NY," AND Contains "NY-ON" etc. no need to complex it, just several IF and ELSE IF statements to get true and false.

[OTrieger]

I think that the key words here is keep it simple. Sometimes using the usual tools with a few simple formulas will solve the issue. I do not mind using regex, but sometimes it just not needed. And if you are not too familiar with regex, while it is important to get familiar with it, find a simple solution that you do master.

[EEnglish2]

@OTrieger 

That's what I was trying to do in my original situation, using Contains.  But if I use "NY," as an exclusionary parameter, I lose anywhere that "NY" appears at the end of the list or is the only entry.  That's where I initially got stuck and the reason I came here for help.  If you know of a way that I can get my desired results using only the Contains function, I would prefer to do it that way and avoid RegEx.  However, it's looking like I'll need to use RegEx in order to find states that also have variants.

Also, if I were able to use that logic in a formula, couldn't I put that logic directly into the filter?

[apathetichell]

No. you cannot do what you are trying to do with just contains - UNLESS you do the unnesting/joining which I described above. Your two options are:

1) unnest (see my instructions)

2) regex (see my instructions)

Choose one.

[OTrieger]

@ Yes, you can as long as there is only one formula in use, however if you use several formulas then no.

That is the simplicity of the Formula tool, you can set a Formula State = "NY" and this will be a condition when you have only NY without any other state.

If it will be at the end of the list then there will be a comma and space before it.
So you can set 3-4 formulas for all possible scenarios and then filter on the Flag.

Keep it simple 🤓

[CoG · Accepted solution]

The problem with using just Contains(), is one that has already been mentioned. If you have a record that only has the state value "NY-ON", that value also contains "NY". You can add commas to resolve this, but as @EEnglish2 has also pointed out, you would need many different cases to cover all possibilities, which is inefficient if you need to be able to search for other states too. The Regex solution is the simplest, and most efficient solution in this case considering the constraints of the problem, requiring only a single function, which can be quite easily made much more dynamic so that any state can be searched for.

Since we are in the second page of responses: REGEX_Match([States],"(?:^|.*,)NY(?:,.*|$)")

This is my proposed solution again, which combines all relevant checks in one function, instead of requiring at least 3 different Contains() functions.

@apathetichell 's response was very similar to mine, but a little more complicated in my opinion.

[apathetichell]

@CoG - yes 100% - your Regex was solid and I wouldn't have posted had I read your post. I think your screent shot though was off and had the ny-on term vs the ny term - and I saw that first.

[OTrieger]

@CoG  and @apathetichell 

If you read my last comment you will see that I'm not only suggesting use Contains but also equals for these scenarios.

I do agree that for those that master or familiar with regex, regex will be the simplest solution, however not everyone knows it well enough. Obviously they should learn how to use regex, till then this issue can be solve with simple formulas.

[EEnglish2]

Thank you @apathetichell and @CoG!  Both of your RegEx recommendations worked and provided the desired output.  However, I will be marking @CoG 's response as the solution, as his RegEx is a little bit cleaner and @apathetichell did acknowledge the original suggestion.