Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Return true/false on [strings in a cell] being found in [strings in another cell]?

Cell A1 contains multiple strings, eg "CAT DOG RAT GNU";

Cell B1 contains multiple strings, eg "RAT CAT";

How can I run a test (using formula in C1) to find if all the strings in B1 are present in cell A1?

  • Returning true/false would be good
  • Strings not necessarily in the same order, as example above
  • The number of items can vary
  • Multiple instances not a problem, so long as they're there
  • But returns true only if all items in cell B1 are present in cell A1.

So far I've tried transposed-split arrays with vlookups and matches, counts, etc, but nothing working for me. (And maybe regex won't do it as can't loop for each string?)

Solution

  • you can try: 

    =ARRAYFORMULA(IF(PRODUCT(N(NOT(ISNA(REGEXEXTRACT(SPLIT(B1, " "), 
 SUBSTITUTE(A1, " ", "|"))))))=1, TRUE))

    0

    for more precision you can do: 

    =ARRAYFORMULA(IF(PRODUCT(N(NOT(ISNA(REGEXEXTRACT(SPLIT(B1, " "), 
 "^"&SUBSTITUTE(A1, " ", "$|^")&"$")))))=1, TRUE))

    0

    then for case insensivity:

    =ARRAYFORMULA(IF(PRODUCT(N(NOT(ISNA(REGEXEXTRACT(SPLIT(LOWER(B1), " "), 
 "^"&SUBSTITUTE(LOWER(A1), " ", "$|^")&"$")))))=1, TRUE))

    0

    and true ArrayFormula would be:

    =ARRAYFORMULA(IF((A1:A<>"")*(B1:B<>""), IF(REGEXMATCH(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
 REGEXMATCH(IF(SPLIT(B1:B, " ")<>"", SPLIT(LOWER(B1:B), " "), 0), 
 "^"&SUBSTITUTE(LOWER(A1:A), " ", "$|^")&"$"))),,999^99)), "FALSE"), FALSE, TRUE), ))

    0