Extract Keyword from text string

[LambertyE]

Hi, I am looking on how to simplify this formula since the list of keywords keep growing and the formula longer. What I am trying to do is extract a keyword from a free type text. 1. Create a list of keywords in A1:A36. 2. Enter unformatted text in B1, B2, to B100 (just as an example) 3. Return results of match of keywords in A1:A20 in C1, C2, etc to C100. So, spreading this out: 1. Check the text string in B1 for the ONE text string in A1, and return this in C1. 2. Check the text string in B1 for the ONE text string in A2, and return this in C2, and continue so that C3, C4, C5 etc are the matches against A1:A20 in B1. 3. Repeat for entire range A1:A20 against B2 to B100. So, I applied the following formula but as I mentioned above the keyword list keeps growing and concern about the accuracy of the formula if I keep adding to the function of it. =TRIM(IF(ISNUMBER(SEARCH($Z$2,AA2)),$Z$2,"")&" "&IF(ISNUMBER(SEARCH($Z$3,AA2)),$Z$3,"")&" "&IF(ISNUMBER(SEARCH($Z$4,AA2)),$Z$4,"")&" "&IF(ISNUMBER(SEARCH($Z$5,AA2)),$Z$5,"")&" "&IF(ISNUMBER(SEARCH($Z$6,AA2)),$Z$6,"")&" "&IF(ISNUMBER(SEARCH($Z$7,AA2)),$Z$7,"")&" "&IF(ISNUMBER(SEARCH($Z$8,AA2)),$Z$8,"")&" "&IF(ISNUMBER(SEARCH($Z$9,AA2)),$Z$9,"")&" "&IF(ISNUMBER(SEARCH($Z$10,AA2)),$Z$10,"")&" "&IF(ISNUMBER(SEARCH($Z$10,AA2)),$Z$10,"")&" "&IF(ISNUMBER(SEARCH($Z$11,AA2)),$Z$11,"")&" "&IF(ISNUMBER(SEARCH($Z$12,AA2)),$Z$12,"")&" "&IF(ISNUMBER(SEARCH($Z$13,AA2)),$Z$13,"")&" "&IF(ISNUMBER(SEARCH($Z$14,AA2)),$Z$14,"")&" "&IF(ISNUMBER(SEARCH($Z$15,AA2)),$Z$15,"")&" "&IF(ISNUMBER(SEARCH($Z$16,AA2)),$Z$16,"")&" "&IF(ISNUMBER(SEARCH($Z$17,AA2)),$Z$17,"")&" "&IF(ISNUMBER(SEARCH($Z$18,AA2)),$Z$18,"")&" "&IF(ISNUMBER(SEARCH($Z$19,AA2)),$Z$19,"")&" "&IF(ISNUMBER(SEARCH($Z$20,AA2)),$Z$20,"")&" "&IF(ISNUMBER(SEARCH($Z$21,AA2)),$Z$21,"")&" "&IF(ISNUMBER(SEARCH($Z$22,AA2)),$Z$22,"")&" "&IF(ISNUMBER(SEARCH($Z$23,AA2)),$Z$23,"")&" "&IF(ISNUMBER(SEARCH($Z$24,AA2)),$Z$24,"")&" "&IF(ISNUMBER(SEARCH($Z$25,AA2)),$Z$25,"")&" "&IF(ISNUMBER(SEARCH($Z$26,AA2)),$Z$26,"")&" "&IF(ISNUMBER(SEARCH($Z$27,AA2)),$Z$27,""))&" "&IF(ISNUMBER(SEARCH($Z$28,AA2)),$Z$28,"")&" "&IF(ISNUMBER(SEARCH($Z$29,AA2)),$Z$29,"")&" "&IF(ISNUMBER(SEARCH($Z$30,AA2)),$Z$30,"") &" "&IF(ISNUMBER(SEARCH($Z$31,AA2)),$Z$31,"")&" "&IF(ISNUMBER(SEARCH($Z$32,AA2)),$Z$32,"")&" "&IF(ISNUMBER(SEARCH($Z$33,AA2)),$Z$33,"")&" "&IF(ISNUMBER(SEARCH($Z$34,AA2)),$Z$34,"")&" "&IF(ISNUMBER(SEARCH($Z$35,AA2)),$Z$35,"")&" "&IF(ISNUMBER(SEARCH($Z$36,AA2)),$Z$36,"") Any way this can be simplified? Help please! Thanks in advance! Evelyn L

 

[Dag2]

Keywords_Search_Klemens.xlsm
	A	B	C	D
1	Keyword	Text	Result prior	Wanted result
2	IT-Strategie	Die IT-Strategie ist Teil der Gesamtgeschäftsstrategie.	it-strategie, gesamtgeschäftsstrategie	IT-Strategie, Gesamtgeschäftsstrategie
3	Gesamtgeschäftsstrategie	Die IT-Strategie ist kein Add-ON.	it-strategie, add-on	IT-Strategie, Add-ON
4	Add-ON	LOW ist ein Add-ON.	low, add-on	LOW, Add-ON
5	LOW
Sheet1

Here´s an example that should clarify my question. Thanks in Advance!

 

[Peter_SSs]

Here´s an example that should clarify my question.

Thanks. Try this version.
Is it possible that the same keyword could appear more than once in a column B cell?
If so, should that keyword be repeated in the results?
If not, I think that the function could be simplified a little more.

Function GetWords(KeyWds As Range, s As String) As String
  Dim RX As Object, d As Object, M As Object
 
  Set d = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b(" & Join(Application.Transpose(KeyWds), "|") & ")\b"
  For Each M In RX.Execute(s)
    d(CStr(M)) = 1
  Next M
  GetWords = Join(d.keys, ", ")
End Function

Dag2.xlsm
	A	B	C
1	Keyword	Text	Wanted result
2	IT-Strategie	Die IT-Strategie ist Teil der Gesamtgeschäftsstrategie.	IT-Strategie, Gesamtgeschäftsstrategie
3	Gesamtgeschäftsstrategie	Die IT-Strategie ist kein Add-ON.	IT-Strategie, Add-ON
4	Add-ON	LOW ist ein Add-ON.	LOW, Add-ON
5	LOW
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=GetWords(A$2:A$5,B2)

 

[Dag2]

Thanks. Try this version.
Is it possible that the same keyword could appear more than once in a column B cell?
If so, should that keyword be repeated in the results?
If not, I think that the function could be simplified a little more.

Function GetWords(KeyWds As Range, s As String) As String
  Dim RX As Object, d As Object, M As Object
 
  Set d = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b(" & Join(Application.Transpose(KeyWds), "|") & ")\b"
  For Each M In RX.Execute(s)
    d(CStr(M)) = 1
  Next M
  GetWords = Join(d.keys, ", ")
End Function

Dag2.xlsm
	A	B	C
1	Keyword	Text	Wanted result
2	IT-Strategie	Die IT-Strategie ist Teil der Gesamtgeschäftsstrategie.	IT-Strategie, Gesamtgeschäftsstrategie
3	Gesamtgeschäftsstrategie	Die IT-Strategie ist kein Add-ON.	IT-Strategie, Add-ON
4	Add-ON	LOW ist ein Add-ON.	LOW, Add-ON
5	LOW
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=GetWords(A$2:A$5,B2)

Thank you so much, that was it!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.