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.
"foo blah blah"
- then the formula returns TRUE (both contain
- and conditional formatting is applied to
I tried the following formula but it returns TRUE only if
B1="foo" and FALSE if
"foo" is a substring:
Any help would be greatly appreciated.
I realize that I left out important details for this problem. I also need this formula to:
- conditionally apply across an entire cell range
- be case-insensitive
- allow a comma-separated keyword list on cell
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", " ", "|"))