How to Capture multiple Keywords in paragraph

[mrcris]

	A	B	B
1	keyword	paragraph	Expected Result
2	Lorem	Lorem ipsum dolor sit amet, consectetur adipiscing elit	Lorem, elit
3	adipiscing elit	Lorem ipsum dolor sit amet, consectetur adipiscing elit	lorem, adipiscing elit, dolor sit ame, elit
4	dolor sit ame
5	elit

<tbody>
</tbody>

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

 

[shg]

B2 and B3 are the same. Wouldn't you expect the same result in C2 and C3?

 

[mrcris]

keyword	Paragraph	Expected Result
Lorem <colgroup><col></colgroup><tbody> </tbody>	Lorem ipsum dolor sit amet, consectetur adipiscing elit <tbody> </tbody>	Lorem, amet, adipiscing elit <tbody> </tbody>
Amet <tbody> </tbody>	Vivamus iaculis id libero ut pulvinar, consectetur adipiscing elit <tbody> </tbody>	Iaculis, Adipiscing elit <tbody> </tbody>
Iaculis <tbody> </tbody>	Aenean quis cursus lectus, dolor sit ame <tbody> </tbody>	Dolor sit ame <tbody> </tbody>
adipiscing elit <tbody> </tbody>
dolor sit ame

<tbody>
</tbody>

Hi Mr shg, just like this, could you support me? thanks you so much

 

[shg]

	A​	B​	C​	D​
1​	keyword​	paragraph​	Expected Result​
2​	Lorem	Lorem ipsum dolor sit amet, consectetur adipiscing elit	Lorem, Amet, adipiscing elit, dolor sit ame	C2: {=CatIf(ISNUMBER(SEARCH($A$2:$A$6, B2)), $A$2:$A$6, ", ")}
3​	Amet	Vivamus iaculis id libero ut pulvinar, consectetur adipiscing elit	Iaculis, adipiscing elit
4​	Iaculis	Aenean quis cursus lectus, dolor sit ame	dolor sit ame
5​	adipiscing elit
6​	dolor sit ame

Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",", _
               Optional bCatEmpty As Boolean = False) As String
  ' shg 2007
  ' UDF only

  ' Catenates the elements of rInp separated by sSep where the corresponding
  ' element of avbIf is True. Empty cells ignored unless bCatEmpty is True.

  Dim iRow          As Long
  Dim iCol          As Long
  Dim i             As Long

  If TypeOf avbIf Is Range Then avbIf = avbIf.Value
  If Not IsArray(avbIf) Then avbIf = Array(False, avbIf)
  
  On Error Resume Next
  i = UBound(avbIf, 2)

  If Err.Number Then
    ' avbIf is 1D
    For iRow = 1 To rInp.Rows.Count
      For iCol = 1 To rInp.Columns.Count
        i = i + 1
        If avbIf(i) Then
          If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
            CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
          End If
        End If
      Next iCol
    Next iRow
  Else
    ' it's 2D
    For iRow = 1 To rInp.Rows.Count
      For iCol = 1 To rInp.Columns.Count
        If avbIf(iRow, iCol) Then
          If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
            CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
          End If
        End If
      Next iCol
    Next iRow
  End If

  If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function

 

[mrcris]

hi MR SHG, I run follow you, but the result as below. This is my step
1. Alt F11 - > insert -> module - > paste code
2. Put =CatIf(ISNUMBER(SEARCH($A$2:$A$6,B2)),$A$2:$A$6,", ") to C2

Could you let me know what i wrong? Thanks you

keyword	Paragraph	Expected Result
Lorem <tbody> </tbody>	Lorem ipsum dolor sit amet, consectetur adipiscing elit <tbody> </tbody>	Lorem, Amet, Iaculis, adipiscing elit, dolor sit ame <tbody> </tbody> <tbody> </tbody>
Amet <tbody> </tbody>	Vivamus iaculis id libero ut pulvinar, consectetur adipiscing elit <tbody> </tbody>	Amet, Iaculis, adipiscing elit, dolor sit ame <tbody> </tbody> <tbody> </tbody>
Iaculis <tbody> </tbody>	Aenean quis cursus lectus, dolor sit ame <tbody> </tbody>	Amet, Iaculis, adipiscing elit, dolor sit ame <tbody> </tbody> <tbody> </tbody>
adipiscing elit <tbody> </tbody>
dolor sit ame

<tbody>
</tbody>

 

[shg]

The formula needs to be confirmed with Ctrl+Shift+Enter rather than just Enter.

 

[mrcris]

yes i can do it, many thanks you MR SHG

 

[shg]

You're welcome.

 

[mrcris]

Dear mr SHG
I have more question. How to find in case text must exactly?
E.g.


Lorem ipsum dolor sit amet, consectetur adipiscing elit


Keyword: it
You can see the paragraph didn't "it"


But Expected result still show it. maybe it get from "sit"


How to resolve it MR SHG ? Thanks you

 

[shg]

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, ",")