0

I have a huge amount of data that has no consistent sentence formatting, and I have a list of keywords I refer to. Whenever a keyword exists in the data, I want to extract them out onto another column. examples of how I want keywords to be extracted I don't mind if it gives "red apple" and "apple"

Currently I'm just using filters to find cells that has the same words, so I can key in extracted keywords as a batch/group

David Leal
  • 6,373
  • 4
  • 29
  • 56
Bing Shuen
  • 23
  • 5
  • For first one, you have both `apple` and `red apple`. It should return both as result. – Harun24hr Dec 05 '22 at 05:27
  • @BingShuen what is your excel version? If it is not the current one, then it needs to be indicated as a tag in your question. [Here](https://bettersolutions.com/excel/functions/updates.htm) you can find the functions available based on version – David Leal Dec 05 '22 at 19:29
  • @DavidLeal Thank you for your comment. the current version im using is Professional Plus 2019 and for some reason I can't update. I will look at the link you commented with. Again, appreciate your help – Bing Shuen Dec 06 '22 at 01:45
  • As @Harun24hr pointed out it is possible to find both `apple` and `red apple` for row one, but I am not aware on how to just find `red apple` considering also your excel version limitations. – David Leal Dec 06 '22 at 04:06
  • I dont mind if it outputs apple in 1 column, and red apple the next. And ya my excel version is 2019. clicked update but it says im using the latest – Bing Shuen Dec 08 '22 at 08:01

1 Answers1

1

Try the following formula-

=BYROW(A2:A4,LAMBDA(x,TEXTJOIN(", ",1,FILTER(F2:F5,COUNTIFS(x,"*"&F2:F5&"*")>0))))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36