Solved: Stumped: Text Search / Partial Match - Page 2

Michael_Millet1 · 03-09-2020 01:28 PM

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

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],
                ".",
                " "
              ),
              ",",
              " "
            )
            "(",
            " "
          )
          ")",
          " "
        )
        "-",
        " "
      ),
      " "
    )
  )
)

View solution in original post