New answers tagged regex
0
Alternative 1
Instead of using comparison like B2=Casula!$A:$A use NOT(ISNA(MATCH(B2,Casula!$A$2:$A,0))).
Example
=IF(NOT(ISNA(MATCH(B2,Casula!$A$2:$A,0))),"Casula",IF(NOT(ISNA(MATCH(B2,Miranda!$A$2:$A,0))),"Miranda",IF(NOT(ISNA(MATCH(B2,Parramatta!$A$2:$A,0))),"Parra",IF(NOT(ISNA(MATCH(B2,Leichhardt!$A$2:$A,0))),"Leich","NOT IN USE"))))
NOTE: Using long ...
1
In C2 enter this formula :
=filter(
ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A)),
ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))<>""
)
Breakdown of the formula :
=REGEXMATCH(A2,TEXTJOIN("|",true,B2:B))
Regexmatch returns TRUE or FALSE if A2 contains any of the words in column B. TEXJOIN ...
0
Try this in F10 for the tenth row :
=textjoin(",",false,ArrayFormula(iferror(REGEXEXTRACT(A10,TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(E10, ","))))))))
Basically REGEXTRACT() accept arrays.
Top 50 recent answers are included
