Spreadsheet (GSheet) formula to check content in a column

user_19112706 0
Viewed 39

I have rows with sentences. I need to check if at least one word of each sentence is in the column "Word Check" indicating so in each row. I tried with different formulas with no success. Can you help me please? Thank you. enter image description here

spreadsheet google-sheets contains
2 Answers

I found how to: =IF(REGEXMATCH(H18,TEXTJOIN("|",1,$J$17:J26)),"Yes","")

user_191127060

With the rest of the 'Contains' column cleared, you can use this to check any phrase in A2:A against the word list in C2:C. There's only one L in 'Colorful', though, so if there are spelling errors in the Word Check list, they may not be checked correctly against the phrases.

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   IF(
    REGEXMATCH(
     A2:A,
     "(?i)"&TEXTJOIN("|",TRUE,C2:C)),
    "Yes",)))

This will wrap the words with hearts as a delimiter so you can pull exact matches only.

=ARRAYFORMULA(
  IF(ISBLANK(H18:H),,
   IF(
    REGEXMATCH(
     REGEXREPLACE(H18:H,"(\w+|\?\.)","♥$1♥"),
     "(?i)"&
     TEXTJOIN(
      "|",
      TRUE,
      REGEXREPLACE(J18:J,"(\w+|\?\.)","♥$1♥"))),
    "Yes",)))
user_122540980
Related