0

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the respective key search term cells into and array of trimmed text values (eg cell E10 would be ["stuff", "food fluff", "green"]. 2) Regex match the exact pattern contained under the tags column (column F).

The desired result in the second image depicted below will be a filter that only allows the article title "Hating on Food: A Viable Source of Catharsis?" to get through. Note that "Being Cool: A Guide" did NOT make the cut because although it contains the word "food" in its corresponding key search terms cell it is in the context of "food fluff" which is a distinct value from "food" in isolation.

I could puzzle over this for a while but I have a client that needs a quick solution and I'm having trouble seeing how to realize this result. I think I have completed part step one with the following formula: ARRAYFORMULA(TRIM(SPLIT(E10, ","))) This shows the parsing of a single cell into an array from its CSV value-- in this case "stuff", "food fluff", "green".

PS here is a link to the sheet in question

Tags to Parse

enter image description here

Improve this question

2 Answers 2

Reset to default
0

Try this in F10 for the tenth row : 

=textjoin(",",false,ArrayFormula(iferror(REGEXEXTRACT(A10,TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(E10, ","))))))))

Basically REGEXTRACT() accept arrays.

Improve this answer
3
  • This merely yields ',,' in the cell I paste it into. I've added a link to the sheet in my original question if you want to fiddle with it.
    – Shadow43375
    Aug 21, 2019 at 20:22
  • I just edited your sheet : is this your expected result ? I have to be honest : "Note that "Being Cool: A Guide" did NOT make the cut because although it contains the word "food" in its corresponding key search terms cell it is in the context of "food fluff" which is a distinct value from "food" in isolation." I don't understand at all where you find "food" in the string "Being Cool: a Guide". Maybe try to give a before/after screenshot so we can have a better understanding of your needs.
    – pjmg
    Aug 21, 2019 at 21:56
  • "Food" is not contained in "Being Cool: a Guide." It is contained (as a substring) in the key search terms column associated with the row containing "Being Cool: a Guide." ["stuff","food fluff", "green"]. But we don't want sub-strings to count as a match. Only case insensitive and trimmed exact matches of the csv values. Hence "Hating on Food: A Viable Basis of Catharsis" made the cut (because its csv array is ["stuff","food","green"])-- an exact match (ie "food") is found. Also, I included a second screen shot that shows this result in a separate sheet
    – Shadow43375
    Aug 22, 2019 at 6:04
0

true ArrayFormula:

=ARRAYFORMULA(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IFERROR(IF(REGEXMATCH(LOWER(A5:A10), 
 SPLIT(SUBSTITUTE(E5:E10, ", ", "♦"), "♦")), 
 SPLIT(SUBSTITUTE(E5:E10, ", ", "♦"), "♦"), ))),,999^99))), " ", ", "))

0

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.