Checkbox if another field contains text, but don't check if it contains other text

Shaun Dickson

I'm trying to check a box if it contains one of several text strings. But if it contains another subset of text strings, I want the box to NOT be checked. I got it working for the finding text portion, but I can't figure out how to make it remain unchecked if the other text strings are found. Here is the first string that worked:

=IF(OR(FIND("SWG", [Equipment ID]@row) >= 1, FIND("TRN", [Equipment ID]@row) >= 1, FIND("SUS", [Equipment ID]@row) >= 1, FIND("DPL", [Equipment ID]@row) >= 1, FIND("TRN", [Equipment ID]@row) >= 1, FIND("LDP", [Equipment ID]@row) >= 1, FIND("VFD", [Equipment ID]@row) >= 1, FIND("RDP", [Equipment ID]@row) >= 1, FIND("PNL", [Equipment ID]@row) >= 1), 1, 0)

So now I want to modify this so that it will return a 0 if the reference contains "ELV", as an example, as well as a couple other words. I'm wondering if I need to do an AND statement nested in the IF, returning a 0 if the other statements are found? Anyone have advice?

Lloyd Anders

Hey Shaun,

From my understanding, if the text contains any of the subset of "non-check" strings, then you want the box to be unchecked, otherwise if the text contains any of the subset of "check" strings, then you want the box to be checked, and otherwise you want the box to be unchecked.

If the above is correct, then you should be able to use a nested if statement - something like:

=IF({non-check subset conditions}, 0, IF({check subset conditions}, 1, 0))

Another approach would be:

=IF(AND(NOT({non-check subset conditions}), {check subset conditions}), 1, 0)

Additionally, you may want to look into using the CONTAINS function, as this would convert FIND("SWG", [Equipment ID]@row) >= 1 into CONTAINS("SWG", [Equipment ID]@row).

Hope this helps,

Lloyd

Lloyd Anders

Hey Shaun,

From my understanding, if the text contains any of the subset of "non-check" strings, then you want the box to be unchecked, otherwise if the text contains any of the subset of "check" strings, then you want the box to be checked, and otherwise you want the box to be unchecked.

If the above is correct, then you should be able to use a nested if statement - something like:

=IF({non-check subset conditions}, 0, IF({check subset conditions}, 1, 0))

Another approach would be:

=IF(AND(NOT({non-check subset conditions}), {check subset conditions}), 1, 0)

Additionally, you may want to look into using the CONTAINS function, as this would convert FIND("SWG", [Equipment ID]@row) >= 1 into CONTAINS("SWG", [Equipment ID]@row).

Hope this helps,

Lloyd

Shaun Dickson

Thank you Lloyd, your understanding of what I'm trying to do is correct. Thanks for clearing up the CONTAINS as well. I tried that initially but wasn't getting it to work, switching to that cleans it up a lot.

This method is working for me. Thank you very much for the help.