Home > Blockchain >  FILTER REGEXMATCH REGEXREPLACE including all special characters in a case sensitive analysis
FILTER REGEXMATCH REGEXREPLACE including all special characters in a case sensitive analysis

Time:06-29

In cases specific special characters could be added as follows:

=FILTER(D1:D, REGEXMATCH(D1:D, "^("&REGEXREPLACE(TEXTJOIN("|", 1, A:A),"([().])","\\$1")&")$")=FALSE)

But when wanting to analyze all of them, the best approach would be a negative match, fetching all the different values of A-Z, a-z, and 0-9.

So, I tried to reproduce it with a direct addition that finds the values but when matching the values, it returns the number of matches to zero:

=FILTER(D1:D, REGEXMATCH(D1:D, "^("&REGEXREPLACE(TEXTJOIN("|", 1, A:A),"([^A-Za-z0-9])","\\$1")&")$")=FALSE)

What is the correct approach in this case?

Google Sheets data with expected result

CodePudding user response:

=FILTER(D1:D, 
  REGEXMATCH(D1:D, 
    "^("&TEXTJOIN("|", 1, 
      REGEXREPLACE(A:A,"[.* ?^${}()|[\]\\]","\\$0"))&")$")=FALSE)

Regular expression of all special characters: [.* ?^${}()|[\]\\]

Reference:

Escaping

  • Related