Search and pull Keywords from text string

[rajtak]

Hello Everyone,

Would it be possible to pull specific keywords from a text string.

As an example, you will find list of keywords in Column D and the result that I am expecting is in column B. For example: 'wowhead' is listed as a keyword in column D and it is found in the first title so the result should be 'wowhead' in column B, similarly, 'property' and 'certificates' are also listed in column D and both keywords are mentioned in title 2 hence the result should be 'property, certificates' in column B and so on.

Please let me know if this can be achieved by using excel formula or VB script.

 

[Rick Rothstein]

3) My function is case insensitive which I assumed you wanted given you had "wowhead" in the Keyword List and it matched "Wowhead" in cell A2 for your example data. I can change this if you want.

The code I posted in Message #10 is, as mentioned above, strictly case insensitive. Below is that code modified to add an optional Boolean argument allowing you to specify whether you want the Keyword List's words to be case sensitive or not. The default if you omit this argument is FALSE meaning it will work identically to the UDF that I posted in Message #10 (of course you could also explicitly pass a value of FALSE for this argument if you wish. To make the code search case sensitive, you must pass TRUE for the third argument. Here is the modified UDF code...

[table="width: 500"]
[tr]
[td]

Function GetWords(ByVal Text As String, WordList As Variant, Optional MatchCase As Boolean) As String
  Dim Wcase As String, Pattern As String, W As Variant, Words As Variant
  If Not MatchCase Then Text = UCase(Text)
  For Each W In WordList
    If Not MatchCase Then
      Wcase = UCase(W)
      Pattern = "[!A-Z]"
    Else
      Wcase = W
      Pattern = "[!A-Za-z]"
    End If
    If " " & Text & " " Like "*" & Pattern & Wcase & Pattern & "*" Then
      If Len(W) Then GetWords = GetWords & ", " & W
    End If
  Next
  GetWords = Mid(GetWords, 3)
End Function

[/td]
[/tr]
[/table]

Note: If you will never need case sensitive searches, then use the code from Message #10 as it is a smidgeon more efficient than the above code.

 

[rajtak]

Thank you so much Rick for your help

 

[Rick Rothstein]

Thank you so much Rick for your help

You are quite welcome, but... you are only just seeing my responses 9 months after I made them?