Contains "Kansas" is returning TRUE when actually contains ARKANSAS

Catherine Hall

My State field is multi-select and users can select:

Kansas

or

Arkansas

or

Kansas and Arkansas

or

other.

I want to return a text value "Is Kansas" in a separate column if the State column contains Kansas or contains Kansas and Arkansas but will not return "Is Kansas" if the State column has only Arkansas (or other).

When I try a "Contains" formula it happily returns TRUE if it finds Arkansas.

When I try a COUNTIF to see how many kansases it finds, it only returns "1" if there is only one selection and that one selection happens to be Kansas.

When I try FIND, it just gives me "0" unless , again, it's just one Kansas and not multiple states.

States may or may not be in order alphabetically. And there may be 1 or up to 50 states in any given cell.

I just want to be able to detect when Kansas exists.

Any help/suggestions are GREATLY appreciated! :)

Catherine Hall

OK! figured it out!

=IF(FIND("KANSAS", SUBSTITUTE([State/Territory]@row, "ARKANSAS", "ARK")) > 0, 1, 0)

Catherine Hall

I have this at the moment:

=IF(AND(CONTAINS("Kansas", [State/Territory]@row), NOT(CONTAINS("Arkansas", [State/Territory]@row))), 1, 0)

which works on all cells except the ones that contain Kansas AND Arkansas.

If I add another condition to allow Kansas and Arkansas:

=IF(OR(AND(CONTAINS("Kansas", [State/Territory]@row), NOT(CONTAINS("Arkansas", [State/Territory]@row))), AND(CONTAINS("Kansas", [State/Territory]@row), CONTAINS("Arkansas", [State/Territory]@row))), 1, 0)

now I get false positives when only Arkansas is present (no Kansas).....

Catherine Hall

OK! figured it out!

=IF(FIND("KANSAS", SUBSTITUTE([State/Territory]@row, "ARKANSAS", "ARK")) > 0, 1, 0)