# Formula that looks for any matching substrings between two cells

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", " ", "|"))
``````

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().

• I've added some clarifying comments to my question above, and the following formula works: =regexmatch(\$A\$1, substitute("(?i)\b(" & B1 & ")\b", " ", "|")) Sep 21 at 23:53
• Glad that you have a solution. Going forward, please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Sep 22 at 8:11

@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.