A whole AND exact key word match in a large text stream

I have two columns:

Column A1:A300 contains one key word (e.g. water, bar, etc) per cell for each of the 300 cells.

Column B1:B1000 contains various names – can be one or more words (e.g. waterfront barman, water mechanic, bar etc) per cell for each of the 1000 cells

I am trying to determine if each text phrase in Column B contains any WHOLE word (but exact match) from Column A.

So in my example above, the cell with a phrase (e.g. waterfront barman) would return a False value since it does not contain any of the EXACT WHOLE words from column A. On the other hand, a cell with a phrase (water mechanic) would return the value True since this phrase contains one whole and exact match (mechanic) in Column A.

Also, if a phrase from Column B contains more than one word from Column A, it does not need to count the number of instances. It simply needs to return the value True to indicate that at least one of the words from Column A is contained within the word from Column B.

In addition, could you please provide two solutions (if possible):
1. For case sensitive match; and
2. Non-case sensitive match.

Thanks for your help
P.S. this is my first tread so my apologies in advance if I have not been clear about my issue.

In cell C1 use the formula:

=NOT(ISNA(MATCH(TRUE,INDEX(ISNUMBER(FIND(" " & $A$1:$A$300 & " ", " " & B1 & " ")),0),0)))

This is a case sensitive search, to make it non-sensitive change it to:

=NOT(ISNA(MATCH(TRUE,INDEX(ISNUMBER(FIND(" " & UPPER($A$1:$A$300) & " ", " " & UPPER(B1) & " ")),0),0)))

A condition of this is that whole words are always delimited by a space. If your job titles include commas, full stops, hyphens, quotes, etc. then the formula will have to be refined.

Hi Andrew

Thank you very much for your prompt response.
I have tested both formulas and they work perfectly with the whole words delimited by a space as you explained above. However, from time to time I have to download various key words that contain various symbols. At any time I may have job titles with commas, full stops and hyphens.

Would it be hard for you to refine these two above formulas to account for these situations?
Many thanks in advance.

Hi Andrew

When I mentioned “downloading key words” in my previous response I meant job titles in Column B.

Regards

There are three ways around this that I can think of off the top of my head:

1. When you import your job titles use a macro to clean them. This would replace breaking characters with a space, meaning the formula would work. The downside is that it would change the job titles, so if you need them to stay exactly the same then this wouldn't work.

2. Modify the formula to substitute spaces for breaking characters when it runs. For example, the following formula will treat full stops as spaces:

=NOT(ISNA(MATCH(TRUE,INDEX(ISNUMBER(FIND(" " & $A$1:$A$300 & " ", " " & SUBSTITUTE(B1,"."," ") & " ")),0),0)))

And this one will also treat commas as spaces:

=NOT(ISNA(MATCH(TRUE,INDEX(ISNUMBER(FIND(" " & $A$1:$A$300 & " ", " " & SUBSTITUTE(SUBSTITUTE(B1,"."," "),","," ") & " ")),0),0)))

This is probably only a good way forward if there are a fairly small number of characters that are likely to be encountered.

3. Write a UDF (User-Defined Function) to do the search. This isn't particularly hard, but it would mean that your workbook would only work if macros were enabled and a search written in VBA is going to be slower than a search using Excel's native functions, which may be an issue if you have thousands of rows of data.

Let me know which option you think sounds most suitable for you and I'll help you out with implementing it.

Hi Andrew
Thank you very much for this detailed explanation. I will have a number of characters and I would be very hard to eliminate them one by one. My only problem is that I am not that good in using VBA. Therefore, if you have time to take me through the whole process (step-by-step) that would be superb. Either way many thanks once again for your assistance and advice in addressing my problem.

Sorry to take so long to get back to you - it was a 4 day weekend here in the UK, so I've been relaxing.

The code for a UDF might look something like this:

Function WordMatch(sSearchText As String, rngSearchRange As Range, Optional bCaseSensitive = False) As Boolean

Const sBREAKING_CHARS = "*,.;:!()<>[]{}"""

Dim sWorkingSearch As String
Dim lBreakLoop As Long
Dim rngMatch As Range
Dim asWordArray() As String
Dim lWordLoop As Long
Dim bTmpReturn As Boolean

sWorkingSearch = sSearchText
For lBreakLoop = 1 To Len(sBREAKING_CHARS)
  sWorkingSearch = Replace(sWorkingSearch, Mid(sBREAKING_CHARS, lBreakLoop, 1), " ")
Next lBreakLoop

sWorkingSearch = Trim(sWorkingSearch)
asWordArray = Split(sWorkingSearch, " ")

bTmpReturn = False

For lWordLoop = LBound(asWordArray) To UBound(asWordArray)
  Set rngMatch = rngSearchRange.Find(asWordArray(lWordLoop), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=bCaseSensitive)
  If Not rngMatch Is Nothing Then
    bTmpReturn = True
  End If
Next lWordLoop

WordMatch = bTmpReturn

End Function

Inserting this into a module in your workbook would allow you to use the following formula in cell C1:

=WordMatch(B1,$A$1:$A$300)

Or, if you wanted to make it case-sensitive then:

=WordMatch(B1,$A$1:$A$300,TRUE)

The first thing that the code does is convert each of the characters listed in the constant sBREAKING_CHARS into spaces in your search string. You can add or remove characters from that string as you need. The string has three quote marks at the end because the quotes character itself will also be treated as a space. If you don't want quotes to delimit a word then change that string to end with just a single quote.

Hope that makes sense, let me know if you need further details.

Hi Andrew
I have tested your code and it works perfectly.
Thank you very much for all the time you have spent to solve my problem.