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:
- conditionally apply across an entire cell range
B1:D1
- be case-insensitive
- 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", " ", "|"))