Search for text in cell, if it exists add to another cell, comma separated

[mdsurf]

Hi! I'm using a CSV file and trying to figure out how to create a specific formula or if it's even possible.

I have a CSV file with a column that contains around 1,500 cells. Each cell contains one written sentence. I have a list of keywords I would like to search for in these cells and if they exist, write them down, comma separated into another cell. Not sure if I'm explaining this clearly so hopefully the example below makes sense.

Keywords to search for, in a separate tab

Football

Soccer

Basketball court

Baseball

Tennis player

Cells with sentence	Formula that searches sentence for keywords and lists the keywords found, comma separated
Jimmy likes to play football on the weekends	football
Sara is an excellent swimmer	None
Carl is a tennis player and also likes to play soccer when he can	tennis player, soccer
The football team often plays baseball in the summer	football, baseball
The school has a basketball court as well as a soccer field	basketball court, soccer
Tomorrow the tennis player will play soccer instead	tennis player, soccer

No idea if this is possible or not, but thank you in advance for the help.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JEC]

For office 365:

Keywords in I1:I5

=TEXTJOIN(", ",TRUE,FILTER($I$1:$I$5,ISNUMBER(SEARCH($I$1:$I$5,A2)),"None"))

Earlier versions: (close with ctr+shift+return, its an array formula). Only thing here is a comma at the end of the string.

=CONCAT(IFERROR(INDEX($I$1:$I$5,IF(ISNUMBER(SEARCH($I$1:$I$5,A2)),ROW($1:$5),""))&", ",""))

 

[jasonb75]

Earlier versions: (close with ctr+shift+return, its an array formula). Only thing here is a comma at the end of the string.

That will only work as far back as 2019 so you could use textjoin instead of concat to eliminate the additional comma at the end.
Versions older than 2019 had concatenate, not concat. The earlier version does not support arrays.

 

[Peter_SSs]

Assuming MS365 and that the whole "actual words" in the key word list need to exist in the text (refer A8 which does not have the word football in it), you could try the modification shown in column B

For all versions and assuming that the 'case' of the final list does not have to match the key word list but can match the case in the original text, you could try this user-defined function which is used in column C. Post back if you need instructions for implementing the udf.

Function WordList(s As String, ListOfWords As Range) As String
  Dim RX As Object, M As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "\b(" & Join(Application.Transpose(ListOfWords), "|") & ")\b"
  If RX.Test(s) Then
    For Each M In RX.Execute(s)
      WordList = WordList & ", " & M
    Next M
    WordList = Mid(WordList, 3)
  Else
    WordList = "None"
  End If
End Function

mdsurf.xlsm
	A	B	C	D					I
1	Cells with sentence								Football
2	Jimmy likes to play football on the weekends	Football	football						Soccer
3	Sara is an excellent swimmer	None	None						Basketball court
4	Carl is a tennis player and also likes to play soccer when he can	Soccer, Tennis player	tennis player, soccer						Baseball
5	The football team often plays baseball in the summer	Football, Baseball	football, baseball						Tennis player
6	The school has a basketball court as well as a soccer field	Soccer, Basketball court	basketball court, soccer
7	Tomorrow the tennis player will play soccer instead	Soccer, Tennis player	tennis player, soccer
8	John is from a footballing family	None	None
9
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=TEXTJOIN(", ",TRUE,FILTER(I$1:I$5,ISNUMBER(SEARCH(" "&I$1:I$5&" "," "&A2&" ")),"None"))
C2:C8	C2	=WordList(A2,I$1:I$5)