1

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

2 Answers 2

2

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

2
  • I've added some clarifying comments to my question above, and the following formula works: =regexmatch($A$1, substitute("(?i)\b(" & B1 & ")\b", " ", "|")) Commented Sep 21, 2023 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. Commented Sep 22, 2023 at 8:11
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.