How to use the IN function flexibly based on a field instead of set values?

[AkisM]

I am trying to filter my data with the IN function. I want to check that my field CODE is does not contain any value from a long list of possible codes. Instead of manually writing down a 10 mile long "[CODE] NOT IN ('US9841983','JF98324723,'JI83872920'....) expression, I just want to use a specific field that contains all those values. I tried creating that field by concatenating my data into a single field with comma as separators and ' at the start and beginning of each value, but when I tried to use the field in the NOT IN expression of the filter tool, the results were not as I expected.

Can someone share a working example below of what I am describing? My original data looks something like this (but a lot more codes)

Code	Column2	Column3
US874932294	Random data	Random data
JI9237820283	Random data	Random data
IF8201827332	Random data	Random data
KS898720923	Random data	Random data
IK8297192902	Random data	Random data
OK092832910	Random data	Random data

And i am trying to create a field that can be used in the argument of the IN function so I don't have to write down 200 different codes.

[atcodedog05]

Hi @AkisM 

You can use join tool like below.

Workflow:

Hope this helps : )

[MarqueeCrew · Accepted solution]

If you are searching for text, I'd use a find replace tool and append the found term.  Then you can easily identify null versus the first term found as IN. 

cheers,

 mark

[AkisM]

Thanks to both for the suggested solutions, they do achieve the exact same thing as what I'm looking for, but just for the sake of curiosity, is it possible to do with the IN function as I'm describing? @MarqueeCrew @atcodedog05 

[atcodedog05]

Hi @AkisM 

Using IN no its not possible. Instead you can use contains like below. Which should the required action.

Contains([List], [Value])

Hope this helps : )