Remove the Characters and the Content between Them Using FILTERXML & SUBSTITUTE

[Anonymous User 51392]

I have a table of input of words such as:

Input​	Result​
Biology[23], Chemistry, Physics[17], History, [21]Geography[11]	Biology, Chemistry, Physics, History, Geography
Mathematics,[27] Science (Terminated), [51]English,[36] History,[79] Music[3]	Mathematics, Science, English, History, Music
Accounting (Suspended), Economics[25]	Accounting, Economics
Arts, Literature (Begins on 7/7/2022)[6]	Arts, Literature
...	...

May I know what formula should I apply in the Result column so that:

1. the square brackets along with the contents inside the square brackets can be removed?
2. the round brackets along with the contents inside the round brackets can be removed?
3. The formula in the result column should be the same for every row and it uses colon for cell reference (e.g. C3:C987) so that drag and fill of formula can be omitted.
4. The result should be as shown in the column on the right.

I tried to use the formula below, but it can only remove the first instance of square bracket and its contents:
=IFERROR(FILTERXML("<a><b>"&IFERROR(SUBSTITUTE(C3:C987,MID(LEFT(C3:C987,FIND("]",C3:C987)),FIND("[",C3:C987),LEN(C3:C987)),""),C3:C987)&"</b></a>","//b"),"")

May I know how can I improve the formula? Or are there any better formula?

 

[DanteAmor]

Hi @Tom.Jones , I confirm that your proposal also works for the 2010 version

 

[Peter_SSs]

Hi @Tom.Jones , I confirm that your proposal also works for the 2010 version

Thanks Dante, must be my failing memory then (unfortunately it is happening with more regular frequency these days).

Other considerations are whether the original data is the result of formulas or not (if formulas Find/Replace would not work) and whether automatic updates are wanted if the data changes without having to do the manual (or vba) process again. I note that the OP has chosen an automatic update via the LAMBDA option.