Digital Farmer
Digital Farmer

Reputation: 2107

FILTER + REGEXMATCH + REGEXREPLACE including all special characters in a case sensitive analysis

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

Upvotes: 0

Views: 825

Answers (1)

idfurw
idfurw

Reputation: 5852

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

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

Reference:

Escaping

Upvotes: 2

Related Questions