The syntax for Calc REGEX allows TEXT to reference a cell where the regular expression is to be applied.
May EXPRESSION also refer to a cell or a range?
If the EXPRESSION must be spelled out “longhand”, how does one specify problematic special characters?
I find that I can filter some special characters but not others.
And depending on the EXPRESSION string length or content,
the Function may end up not filtering ANY of the special characters.
Please provide an example of text you have and which result you want to to get from applying REGEX("text",EXPRESSION...) - of course EXPRESSION can be taken from a cell. If your question is related to “How do I enter characters I want to search for literally, but which have a special meaning with respect to regular expressions”, then you need to mask the special meaning by “” (e.g. if you want to include a square bracket literally ([) into expression, you need to use \[ in expression,).
What are your “special characters” and what expression do you use? Usually unexpected behaviour results from not being aware of how regular expressions actually work. See the ICU regex page’s Metacharacters, Operators and Set Expressions (Character Classes). And yes, of course you can enter an expression in a cell and use just that cell’s reference in the REGEX() function, or any formula expression that produces a regular expression text string.
If, for example, Cell $A$1 contains “@Example” ( without quotes ) and Cell $M$1 contains the @ symbol and I am using the formula =REGEX($L$1,"[$M$1]","",“g”) to try and get the result “Example” ( without quotes ) I still get the results “@Example”. ( By the way, if you literally use the ; semicolon to separate the fields in the formula as the Calc syntax specifies, Calc auto-corrects to using the , comma symbol ) It is only by using the formula =REGEX($L$1,"[@]","",“g”) or =REGEX($L$1,$M$1,"",“g”) that I get the desired results. However, when I use the formula =REGEX($L$1,$M$1:$M$5,"",“g”) to specify a range of cells containing special characters I wish to filter, it does not work. Re: Regular Expression Metacharacters, it is unclear if the special characters * ? + [ ( ) { } ^ $ | \ . are “Inside the set” or “Outside the set” for these purposes. ( And whatever became of the right square bracket ] in that list? ). Ditto [ ] \ - & Inside or Outside? What about others not on either list?
Hi! To filter a range of cells you must combine it to one string. You can use the textjoin function for it.
So please test this function if it solved your problem.
=REGEX($L$1,TEXTJOIN("|",1,$M$1:$M$5),"","g")Thanks for the pointer! I eventually figured that part out myself, including the mandatory use of the pipe delimiter (|) , although I performed the function in a separate cell rather than within the REGEX function. Still parsing the need to mask characters that may have special meanings w/r to “regular expressions”
Fwiw, that
=REGEX($L$1,"[$M$1]","","g")
was wrong anyway because "[$M$1]" is a literal string as regex argument (with a regex that makes no sense), but not a cell reference, which would be $M$1, so
=REGEX($L$1,$M$1,"","g")
may had worked.
it is unclear if the special characters * ? + [ ( ) { } ^ $ | \ . are “Inside the set” or “Outside the set” for these purposes
What set are you talking of?
And whatever became of the right square bracket ] in that list?
I don’t get that question.
Ditto [ ] \ - & Inside or Outside?
Ditto.
Maybe you want to learn and experiment a bit with regular expressions https://regex101.com/ (PCRE are almost identical to the ICU regex).