google-sheets replace google-sheets-formula

Multiple find & replace with inclusions and exclusions

I need to search and replace multiple text strings in phrases. The search strings should be chosen per criteria known as Sites. There is an "include" criterion and an "exclude" criterion.

Criteria

RowNo	List Set (A)	Values for Search (B)	Values for Replace (C)	Inclusions (D)	Exclusions (E)	Search (F)	Replace (G)
(8)	Anime Set	Scenes	Anime Scenes			`=IFERROR(ARRAYFORMULA("^("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")$\\|^("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")(,)\\|(,)("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")(,)\\|(,)("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")$"),)`	`=IFERROR(ARRAYFORMULA("$4$7"&QUERY(C8:C,"SELECT Col1 WHERE Col1 IS NOT NULL")&"$3$6"),)`
(9)	Anime Set	Facts	Facts about Anime	Site 1
(10)	Anime Set	Memes	Memes from Anime		Site 1
(11)	Movie Set	Scenes	Scenes from Movie
(12)	Movie Set	Facts	Movie Facts	Site 2	Site 3
(13)	Movie Set	Memes	Movie Memes
(14)	Music Set	Facts	Facts about Music
(15)	Music Set	Festival	Music Festival	Site 4
(16)	Music Set	App	Music App		Site 4

Source data and desired results:

RowNo	List Set (H)	Belonging (I)	Texts (J)	Replaced Texts (K)
(8)	Anime Set		Scenes,Facts,Memes	Anime Scenes,Facts,Memes from Anime
(9)	Anime Set	Site 1	Scenes,Facts,Memes	Anime Scenes,Facts about Anime,Memes
(10)	Movie Set		Scenes,Facts,Memes	Scenes from Movie,Facts,Movie Memes
(11)	Movie Set	Site 2	Scenes,Facts,Memes	Scenes from Movie,Movie Facts,Movie Memes
(12)	Movie Set	Site 3	Scenes,Facts,Memes	Scenes from Movie,Facts,Movie Memes
(13)	Music Set		Facts,Festival,App	Facts about Music,Festival,Music App
(14)	Music Set	Site 4	Facts,Festival,App	Facts about Music,Music Festival,App

The phrases to process are in column Texts (J) and the desired results are in column Replaced Texts (K).

Some explanations:

Depending on the list set and belonging, search and replace values are applied to the texts;
As you can see above in the table, cells K8 and K9 are distinguished by belonging, but not by list set - so the Facts value is replaced to Facts about Anime only for Site 1(K9, not K8) (in the Table 1, this value is in inclusions and is only applied to texts if there is an belonging match);
Another example with cells K11 and K12: in cell K11 the value Facts is replaced (because it is in the inclusions), but in cell K12 this value is ignored (because it is in the exclusions for this belonging).

Possibly related question

Google Sheets multiple search and replace from a list

Formula I tried(K8):

=IFERROR(LAMBDA(data,re,with,BYROW(data,LAMBDA(r,IF(r="","",REDUCE(r,SEQUENCE(COUNTA(re)),LAMBDA(ini,v,REGEXREPLACE(ini,INDEX(re,v),INDEX(with,v))))))))(QUERY(J8:J,"SELECT Col1 WHERE Col1 IS NOT NULL"),QUERY(A8:I,"SELECT Col6 WHERE Col6 IS NOT NULL AND Col1 IS NOT NULL AND Col1 MATCHES '("&$A$5&")'"),QUERY(A8:I,"SELECT Col7 WHERE Col7 IS NOT NULL AND Col1 IS NOT NULL AND Col1 MATCHES '("&$A$5&")'")),)

Solution

Create a filter to filter by List set, inclusions, and exclusions to create the needed search and replace regexes

=TOROW( 
  FILTER($B$8:$C$16,
    H8=$A$8:$A$16,//List set filter 
    (I8=$D$8:$D$16)+($D$8:$D$16=""),//Inclusions filter
    ($E$8:$E$16<>I8)+(I8="")// Exclusions filter
))

Use sequential regexreplace using REDUCE as explained here

=ARRAYFORMULA( 
  MAP(J8:J14,I8:I14,H8:H14, 
    LAMBDA(j,i,h,
      REDUCE(j,
        BYROW( 
          FILTER($B$8:$C$16,
            h=$A$8:$A$16,
            (i=$D$8:$D$16)+($D$8:$D$16=""),
            ($E$8:$E$16<>i)+(i="")
          ),
          LAMBDA(r, JOIN("🏝",r))
        ), 
        LAMBDA(a,c,
          LET(
            arr,TOCOL(SPLIT(c,"🏝",0)),
            rgx,INDEX(arr,1), 
            repl,INDEX(arr,2),
            REGEXREPLACE(a,rgx,repl)
          )
        )
      )
    )
  )
)