How can I find the number of word matches within a cell?

Question

I have a cell (B) that contains the following URL: https://www.solicitors.co.uk/what-is-on-police-bail-but-not-charged/

I have another cell (A) a that contains a search query: police bail without charge uk

I want to create a formula (C) that counts the number of words from A that also appear in B.

In this example, the result should = 2: police, bail

I suspect this may not be possible in a formula and may require a script.

Answer 1

Use this pattern:

=counta( 
  iferror( 
    filter( 
      transpose( split(A2, "/- .", true) ), 
      match( 
        transpose( split(A2, "/- .", true) ), 
        transpose( split(B2, "/- .", true) ), 
        0 
      ) 
    ) 
  ) 
)

With the sample values you quote, the formula will give a result of 3, because there are three words that match: "police", "bail" and "uk".