Formula that looks for any matching substrings between two cells

Question

In Google Sheets, I'm trying to compose a formula that compares the substrings in two cells.

Substrings in each cell are separated by a space.

The formula should return TRUE if any substring in one cell also matches a substring in the other cell.

I intend to use the formula to conditionally format cells.

For example,

• if A1 contains "foo bar",
• and B1 contains "foo blah blah"
• then the formula returns TRUE (both contain "foo")
• and conditional formatting is applied to B1

I tried the following formula but it returns TRUE only if B1="foo" and FALSE if "foo" is a substring:

 =IF(FIND(B1,A1),1)>0

Any help would be greatly appreciated.

---

Update

I realize that I left out important details for this problem. I also need this formula to:

1. conditionally apply across an entire cell range B1:D1
2. be case-insensitive
3. allow a comma-separated keyword list on cell A1

Thanks to @doubleunary I was able to adapt their answer to suit my specific needs as follows:

=regexmatch($A$1, substitute("(?i)\b(" & B1 & ")\b", " ", "|"))

Answer 1

One way to do that is to create a regular expression, like this:

=regexmatch(A1, substitute("\b(" & B1 & ")\b", " ", "|"))

When B1 is foo bar, the regex is \b(foo|bar)\b.

See regexmatch().

Answer 2

@doubleunary's answer is a good one.

The approach below uses SPLIT to convert the individual cells into arrays of substrings

Substrings to Arrays

=LEN(
   FILTER(
     SPLIT(A1," "),
     COUNTIF(SPLIT(B1," "),
       SPLIT(A1," "))))

1. The FILTER Function returns an array of matching substrings or #N/A if no matches.
2. LEN returns the length of the first matching substring or #N/A.
3. Conditional formatting interprets any non-zero number returned by LEN as TRUE and anything else, including #N/A, as FALSE.