I would like to improve this filter so that it can remove a variable text contained in parentheses, as well as the parentheses themselves.

Like this : Example

The starting point of my formula is :

=filter(C3:C;ISBLANK(B3:B))

I thought a "Substitute" could do the trick but I failed to use it properly, and I don't know if this function can be applied to variable text ?

Thanks in advance for your answers !

CodePudding user response：

Try with this formula that removes every text in between brackets:

=INDEX(REGEXREPLACE(FILTER(C3:C;ISBLANK(B3:B)),"\( .\) ",""))

CodePudding user response：

you can use the SUBSTITUTE function in combination with a filter function. Here's how:

Select the cells you want to filter and remove the text contained in parentheses. In a new column next to the data you want to filter, use the SUBSTITUTE function to remove the text contained in parentheses. The syntax for the SUBSTITUTE function is:

=SUBSTITUTE(A1, "(" & ")", "")

where A1 is the cell containing the data you want to filter and "(" & ")" is the text contained in parentheses that you want to remove.

Filter the data using the new column that contains the filtered text. To remove the original data and keep only the filtered data, you can copy the filtered data and paste it over the original data, or delete the original data and keep only the filtered data.

CodePudding user response：

Try with REGEXEXTRACT.

=FILTER(REGEXEXTRACT(C3:C;"\)(.*)");ISBLANK(B3:B))

CodePudding user response：

Thank you for your answers ; unfortunately the formulas return errors when I apply them :

=SUBSTITUTE(A1, "(" & ")", "")

and

=INDEX(REGEXREPLACE(FILTER(C3:C;ISBLANK(B3:B)),"\( .\) ",""))

gives this (In this two cases the error is not identified)

This formula has a different result:

=FILTER(REGEXEXTRACT(C3:C;"\)(.*)");ISBLANK(B3:B))

it gives that (Here the text variable has been deleted as desired, but the rest of the data is no longer filtered correctly)