How to check a box if exact text is found within a string of text

Jen H.

Hello, I am trying to place a check in a box when some exact text is found within a string of words. I am looking to place a check when "TAC" is found in a string of words.

Here's the formula I am using, but its not working. No error message, but is not selecting the checkbox appropriately.

=IF(HAS([Column Name1]@row, "TAC"), 1, 0)

I was using "Contains" rather than "HAS" but that formula checked the box for all words that contained "TAC" not just the ones that matched exactly. I am stumped why HAS doesn't seem to work the same way.

Is it because "HAS" only works if the word "TAC" is the only word present? The Column Name1 is a drop down column that's populated with content like this: Location Name Medical Center TAC

I just need a box checked if and exact match for the word "TAC" is found within the text string. I tried True and False in a drop down and didn't have any luck with that either.

Thank you for any assistance you can provide!

Carson Penticuff

HAS() will only return true under three conditions.

1. The cell is a multicontact cell and contains a match for the contact
2. The cell is a multiselect dropdown and contains a match
3. The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against.

I am assuming your row in question is a standard Text/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.

This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last "word" in the cell, it would not match in that circumstance as there would only be a space on one side.

=IF(CONTAINS(" TAC ", [Column Name1]@row), 1, 0)

If TAC can appear as the first or last "word" in the cell, this will cover those possibilities as well.

=IF(OR(CONTAINS(" TAC ", [Column Name1]@row), LEFT([Column Name1]@row, 4) = "TAC ", RIGHT([Column Name1]@row, 4) = " TAC"), 1, 0)

If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.

=IF(OR(CONTAINS(" TAC ", [Column Name1]@row), LEFT([Column Name1]@row, 4) = "TAC ", RIGHT([Column Name1]@row, 4) = " TAC", [Column Name1]@row = "TAC"), 1, 0)

If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.

Carson Penticuff

HAS() will only return true under three conditions.

1. The cell is a multicontact cell and contains a match for the contact
2. The cell is a multiselect dropdown and contains a match
3. The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against.

I am assuming your row in question is a standard Text/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.

This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last "word" in the cell, it would not match in that circumstance as there would only be a space on one side.

=IF(CONTAINS(" TAC ", [Column Name1]@row), 1, 0)

If TAC can appear as the first or last "word" in the cell, this will cover those possibilities as well.

=IF(OR(CONTAINS(" TAC ", [Column Name1]@row), LEFT([Column Name1]@row, 4) = "TAC ", RIGHT([Column Name1]@row, 4) = " TAC"), 1, 0)

If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.

=IF(OR(CONTAINS(" TAC ", [Column Name1]@row), LEFT([Column Name1]@row, 4) = "TAC ", RIGHT([Column Name1]@row, 4) = " TAC", [Column Name1]@row = "TAC"), 1, 0)

If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.

Jen H.

EUREKA! That works - I chose this option; =IF(CONTAINS(" TAC ", [Column Name1]@row), 1, 0) and because the word TAC was at the end of the text stream, I edited the above slightly by removing the space after "TAC" but keeping the space in front of it. This made the formula =IF(CONTAINS(" TAC", [Column Name1]@row), 1, 0) and it worked beautifully! Thank you so so much on behalf of me and the entire team.

Warmly, Jen