How to Capture multiple Keywords in paragraph

[mrcris]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]keyword[/TD]
[TD]paragraph[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Lorem[/TD]
[TD]Lorem ipsum dolor sit amet, consectetur adipiscing elit[/TD]
[TD]Lorem, elit[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]adipiscing elit[/TD]
[TD]Lorem ipsum dolor sit amet, consectetur adipiscing elit[/TD]
[TD]lorem, adipiscing elit, dolor sit ame, elit[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]dolor sit ame[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]elit[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Dear everyone,
+ I want to make a formula in excel to extract a list of keywords in a paragraph
+ If the results have multiple keywords, should be separated by a comma.


Anyone can help me to resolve? Many thanks for your support

 

[Rick Rothstein]

I have a different UDF (user defined function) for you to try...

[table="width: 500"]
[tr]
	[td]Function GetKeywords(Paragraph As String, Keywords As Range) As String
  Dim X As Long, KW As Range, Parts() As String
  For Each KW In Keywords
    If InStr(1, Paragraph, KW, vbTextCompare) Then
      Parts = Split(" " & Paragraph & " ", KW, , vbTextCompare)
      If UBound(Parts) > 0 Then
        If Right(Parts(0), 1) Like "[!A-Za-z0-9]" And Left(Parts(0), 1) Like "[!A-Za-z0-9]" Then
          GetKeywords = GetKeywords & ", " & KW
        End If
      End If
    End If
  Next
  GetKeywords = Mid(GetKeywords, 3)
End Function[/td]
[/tr]
[/table]

The first argument is the text to search in and the second argument is the range containing the keywords to test for. So, for the example you posted in your original message (Message #1 ), put this formula in cell C2 and then copy it down as needed...

=GetKeywords(B2,A$2:A$5)

Note: Enter this formula normally

 

[Rick Rothstein]

I have a different UDF (user defined function) for you to try...

[table="width: 500"]
[tr]
	[td]Function GetKeywords(Paragraph As String, Keywords As Range) As String
  Dim X As Long, KW As Range, Parts() As String
  For Each KW In Keywords
    If InStr(1, Paragraph, KW, vbTextCompare) Then
      Parts = Split(" " & Paragraph & " ", KW, , vbTextCompare)
      If UBound(Parts) > 0 Then
        If Right(Parts(0), 1) Like "[!A-Za-z0-9]" And Left(Parts(0), 1) Like "[!A-Za-z0-9]" Then
          GetKeywords = GetKeywords & ", " & KW
        End If
      End If
    End If
  Next
  GetKeywords = Mid(GetKeywords, 3)
End Function[/td]
[/tr]
[/table]

The first argument is the text to search in and the second argument is the range containing the keywords to test for. So, for the example you posted in your original message (Message #1 ), put this formula in cell C2 and then copy it down as needed...

=GetKeywords(B2,A$2:A$5)

Note: Enter this formula normally

There is a minor error in my code... here is the corrected code for you to use (you would use it in the same way as described in Message #11 ).

[table="width: 500"]
[tr]
	[td]Function GetKeywords(Paragraph As String, Keywords As Range) As String
  Dim X As Long, KW As Range, Parts() As String
  Application.Volatile
  For Each KW In Keywords
    If InStr(1, Paragraph, KW, vbTextCompare) Then
      Parts = Split(" " & Paragraph & " ", KW, , vbTextCompare)
      If UBound(Parts) > 0 Then
        If Right(Parts(0), 1) Like "[!A-Za-z]" And Left(Parts(1), 1) Like "[!A-Za-z]" Then
          GetKeywords = GetKeywords & ", " & KW
        End If
      End If
    End If
  Next
  GetKeywords = Mid(GetKeywords, 3)
End Function[/td]
[/tr]
[/table]

 

[mrcris]

Add a space before and after each entry in col A and change the formula to

=CatIf(ISNUMBER(SEARCH($A$2:$A$7, " " & B2 & " ")), $A$2:$A$7, ",")

Thanks you so much mr shg

 

[mrcris]

There is a minor error in my code... here is the corrected code for you to use (you would use it in the same way as described in Message #11 ).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function GetKeywords(Paragraph As String, Keywords As Range) As String
  Dim X As Long, KW As Range, Parts() As String
  Application.Volatile
  For Each KW In Keywords
    If InStr(1, Paragraph, KW, vbTextCompare) Then
      Parts = Split(" " & Paragraph & " ", KW, , vbTextCompare)
      If UBound(Parts) > 0 Then
        If Right(Parts(0), 1) Like "[!A-Za-z]" And Left(Parts(1), 1) Like "[!A-Za-z]" Then
          GetKeywords = GetKeywords & ", " & KW
        End If
      End If
    End If
  Next
  GetKeywords = Mid(GetKeywords, 3)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

hi Rick Rothstein i also tried with your code, but i saw one error

E.g.

Keyword: com
Paragraph: google.com
Expeted result: com

I didn't know why the system can find "com" in google.com

 

[Rick Rothstein]

hi Rick Rothstein i also tried with your code, but i saw one error

E.g.

Keyword: com
Paragraph: google.com
Expeted result: com

I didn't know why the system can find "com" in google.com

It would have helped if you had told us which error message you got (just saying there was an error does not help much). Also showing us how you called the function would also have helped. Given that, I'm just guessing at the error here... the second argument is a range, not a text string, so the keyword "com" needed to be in a cell and that cell (along with any other cells containing keywords) needed to be referenced in the function's second argument. Just so you know, when I tested the function, it found the word "com" as expected.