Using Contains and !Contains to filter a field

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.

Tips and Tricks

Common Use Cases

Accepted answers

CoG

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.

All comments

binu_acs

@EEnglish2 Use = operator instead of contains [State]= "NY"

EEnglish2

Are you suggesting that I say [State]="NY"? That won't work because the [State] field is not necessarily an individual state abbreviation. It might look like my original example because it is a comma separated list of abbreviations. So, it is incredibly rare that the field would only contain "NY". I want to make sure I capture any record where NY might be in the list of states.

rfoster7

I"m sure there's a regexp that can get you what you want.

But sometimes its easiest to split the data into multiple rows, filter for the specific row you want, then limit it with a join.

split and filter.jpg

split and filter example.yxzp

caltang

Perhaps if you provide us a sample dataset close to the original along with your desired output, we can better help brainstorm. At the moment from what you described and showed, I’d wager @binu_acs ’ way is the one. But since you gave more context, it changes everything.

EEnglish2

Based on this sample set of data, I would expect that the following records would be returned as they have NY in the States field:

Douglas Adams

Daniel Brown

Nora Roberts

Bradley Meltzer

Kevin Smith

Harrison Ford

William Murray

Isla Fisher

Elmer Fudd

Samuel Yosemite

Charles Norris

James Rhodes

Pepper Potts

Steven Rogers

Sherman Peabody

Chumley Walrus

Dudley Doright

I would expect the following records to be excluded, as they have only NY-ON in the States field, with no NY present:

Scott Card

Susan Grafton

Mark Hamill

Jessica Rabbit

Bart Conner

Anthony Stark

Nicholas Fury

sample-data.csv

CoG

You can use Filter Tool with the following expression:

REGEX_Match([States],"(?:^|.*,)NY(?:,.*|$)")

Hope this helps and Happy Solving!

Sample Workflow.yxmd

apathetichell

IN.

'NY'- IN('NY','OH') - true

'OH' - IN('NY',OH') - true

'NY-ON' - IN('NY','OH') - false

this is all assuming you have a more substantive matching mechanism than just ny - otherwise you can search for ny at the start, ny at the end and ,ny, via regex_match([states],"^ny,.*|.*,ny,.*|.*ny$")

but assuming this is more complicated you can:

1) add a recordid

2) use text to columns - split states into rows using comma

create a second text input with your match terms.

join. match terms -> state

use select tool to drop fields from the Join tool except recordid.

join recordid-> recordid before text to columns tool.

EEnglish2

Thank you for your response. I believe it's as simple and straightforward as it appears and doesn't require anything more substantive. It's just picking records that contain a specific value. I was hoping to just use the "Contains" function, which works in identifying any state that does not have a variant and the variants themselves. Your RegEx approach looks promising, since it includes the comma and looks at the string in 3 different ways. I'll give that a try.

OTrieger

@EEnglish2

There is a simple solution for it. With a Formula tool create a Flag field and just populate there the 3 conditions that you mentioned about. You will have True and False in that field for each of the rows. Connect it to a Filter tool and filter it based on the Flag Field, on True or False as per what you need. So you do not need to focus on the states but on the condition if it is true or false.

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703