0

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.

0

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".

2
  • You, my friend, are a genius! Works perfectly. Many, many thanks Nov 29 '21 at 23:18
  • Done - thanks for the info Nov 30 '21 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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