Automation to find exact word match in a text field

Brock Moreno

I'm exploring a way to precisely match the word "HE" within a text field used for comments. When the exact word "HE" appears in the text, I want to populate another field named "Gender" with the value "Male." However, I'm encountering a challenge where the search for "HE" also matches it within other words like "SHEET" or "SCHEDULE." I've attempted various methods, including using double and single quotations, brackets, and squiggly brackets, but none of these have resulted in the desired outcome. While I understand that I can create a helper field using a formula to address this, my preference is to achieve this using automation to avoid unnecessary additional fields.

KPH

Hi

I would like to use add a column to the sheet with a formula in it to identify male/female from the text and then use that to update the Patient Gender via the automation (or just use that column instead of automation, depending on your requirements).

Using a formula will give you move control. For example, you could use CONTAINS to only mark cells where there is a space before and after "he", therefor excluding "sheet", "schedule", and "she". You don't have that level of control in the automation.

=IF(CONTAINS(" he ", [Comment/notes]@row), "male")

However, even this will only work if the word is in the middle of a sentence (i.e. with a space either side). As soon as you have a comment "He said..." or "... said he" or "said he." this is not going to work. In those examples there is not a space either side of "he" so it will not be detected. If you add in OR for sentences starting "he " or ending" he" then it all falls apart again and words like "the " or " help" will also be flagged as male.

This could be solved with some additional logic in your IF to mark "he " only at the start of a string and " he" only at the end or if followed by punctuation.

It is a fairly complex to account for all the different ways "he" could be entered and I don't think you can do this with the automation wizard alone.

Paul Newcome

You can account for all ways the letter string "HE" could be used to isolate strictly "HE" and not it being part of a larger string by using a formula to start the string off with a unique character, end the string with that same character, then replace all spaces with that same character.

=SUBSTITUTE("~" + [Column Name]@row + "~", " ", "~")

Now

"He has this sheet"

turns into

"~He~has~this~sheet~"

And

"The sentence has he in it"

turns into

~The~sentence~has~he~in~it~"

Now your automation can look for "~HE~" and it will be picked up no matter where it falls within the string so long as it is strictly "HE" and not part of a larger word.

KPH

@Paul Newcome 's substitution is very cool - killing many birds with one stone 😀. You'd just need to extend it to account for "he" followed by punctuation characters. But it does need to be a column in your sheet.