RegexExtract in Google Sheets for all occurences of seed list?

0
votes

I am trying to extract a comma separated list of matching phrases in Google Sheets.

I have a Named Range 'Matchlist' =

  1. sit amet
  2. Ut enim
  3. commodo consequat

Cell A1 =

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.

In cell B1 the desired output would be:

sit amet,Ut enim,commodo consequat

I have been trying to use REGEXEXTRACT to do, but can only output the first match.... Any ideas or alternatives?

1
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
I would do one at a time (each in own column) and then concatenate the lot using TEXTJOIN() - a-burge
Any ideas on how to do one at a time? I can only ever get the first match from the list; ARRAYFORMULA(PROPER(IFNA(REGEXEXTRACT(LOWER(A1), LOWER(TEXTJOIN("*|", 1, Matchlist)))))) - mrivard

1 Answers

0
votes

try:

=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(REGEXMATCH(IFERROR(SPLIT(IFNA(REGEXREPLACE(A1:A, 
 "(sit amet)|(Ut enim)|(commodo consequat)", "♥$1$2$3♦♥")), "♥")), "♦"), 
 IFERROR(SPLIT(IFNA(REGEXREPLACE(A1:A, 
 "(sit amet)|(Ut enim)|(commodo consequat)", "♥$1$2$3♦♥")), "♥")), ))
 ,,99^99))), "♦$", ), "♦", ","))

0