Stumped: Text Search / Partial Match

[Michael_Millet1]

Hi I have been stumped on this one for a bit.

I have a text column value [Contractor]

I am trying to write an expression that will see if there is a partial match of any text field that is contained in a list [Keywords] so I can apply a format to that row.

Normally, I would just loop through all of the list looking for a partial match Contains() but I am still getting used to this expression only world

Thanks so much in advance.

[Steve · Accepted solution]

There’s no way to do this for an arbitrary list of text fragments.

If you are willing to match whole words, you could do something like:

ISNOTBLANK(
  INTERSECT(
    [Keywords],
    SPLIT(
      [Contractor],
      " "
    )
  )
)

Note that this won’t properly handle words with adjacent punctuation. For instance, Dewey, Cheetum, and Howe would be seen as Dewey,, Cheetum,, and, and Howe. Notice that the first two include the adjacent commas. If your keywords included Dewey and Cheetum, they wouldn’t match here. You could try to handle punctuation with SUBSTITUTE(), but its a horribly ugly and inefficient kludge, like this:

ISNOTBLANK(
  INTERSECT(
    [Keywords],
    SPLIT(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              SUBSTITUTE(
                [Contractor],
                ".",
                " "
              ),
              ",",
              " "
            )
            "(",
            " "
          )
          ")",
          " "
        )
        "-",
        " "
      ),
      " "
    )
  )
)

[Michael_Millet1]

Hi Steve,

Thanks a ton for the solution. I was having trouble wrapping my head around it, even though I had used Split() elsewhere, it never dawned on me. I also used a hybrid of your kludge solution for some of the common punctuation. It’s not perfect but I will just have to constrain the user to using whole words.

Great tech support wizardry