Formula for removing text within multiple sets of parentheses

[mbeyer]

Hello, Mr. (and Mrs.) Excel!

Anyone know of a formula for removing text within multiple sets of parentheses?

I would like to take something like this:
Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N Engl J Med. 2012; 367:1098-1107) and CONFIRM (Fox R et al. N Engl J Med. 2012;367:1087-1097) trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.

and have it read:
Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE and CONFIRM trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.

I wish to automate this task so formulas (if possible) are preferred. Totally cool with a multi-formula, many-columned solution - as long as this task is automated.

NB: Each cell will contain different text, so the sets of parentheses will appear in different places. The number of parenthetical sets may also vary from 1-5.

THANK YOU, THANK YOU!!!!! This board is the best!

 

[XOR LX]

Hi,

Just perform a bulk Find & Replace with Find what: (*) and leave the Replace with: box blank.

Regards

 

[Rick Rothstein]

How about a UDF (user defined function) that will act like a built-in Excel function when used in a formula?

Function NoParens(ByVal S As String) As String
  Dim X As Long, Parts() As String
  S = Replace(S, ")", "(")
  Parts = Split(S, "(")
  For X = 1 To UBound(Parts) Step 2
    Parts(X) = ""
  Next
  NoParens = Application.Trim(Join(Parts, " "))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NoParens just like it was a built-in Excel function. For example,

=NoParens(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

Just perform a bulk Find & Replace with Find what: (*) and leave the Replace with: box blank.

To follow up on XOR LX's suggestion... you should follow this by doing another Find & Replace, this time using two space characters in the "Find what" field and one space character in the "Replace with" field in order to collapse the two adjacent spaces that result from deleting the text in parentheses that existed between them.

 

[XOR LX]

To follow up on XOR LX's suggestion... you should follow this by doing another Find & Replace, this time using two space characters in the "Find what" field and one space character in the "Replace with" field in order to collapse the two adjacent spaces that result from deleting the text in parentheses that existed between them.

Yes. Or just one Find & Replace with Find what: [space](*)

Assuming the string doesn't begin with a parenthesis of course.

Regards

 

[Rick Rothstein]

Yes. Or just one Find & Replace with Find what: [space](*)

Assuming the string doesn't begin with a parenthesis of course.

...and also assuming none of the opening parentheses are preceded by a non-space character such as a quote mark, dash, etc.

 

[XOR LX]

...and also assuming none of the opening parentheses are preceded by a non-space character such as a quote mark, dash, etc.

...which they should never be...

 

[mbeyer]

Thank you very much, Mr. Rothstein!

I am fairly comfortable installing UDFs but thank you very much for the detailed directions - that was very kind.

May I ask another question?

This formula worked wonderfully for the above example! However, some text was returned with an extra space that I couldn't eliminate with the trusty TRIM(CLEAN(SUBSTITUTE(U55,CHAR(160)," "))) formula. U55 was the cell that ran your the awesome NoParens formula.

For example, this:
The phase 3 TOWER trial compared teriflunomide 7 mg/day and 14 mg/day with placebo in patients with MS (Kappos L et al. ECTRIMS 2012. Abstract 153. Miller A et al. 65th Annual Meeting of the American Academy of Neurology [AAN 2013]. Abstract S01.004). The results of this trial showed that, compared with placebo, the 7-mg/day and the 14-mg/day dosages of teriflunomide significantly reduced ARR by 22.3% (P = .0183) and 36.3% (P = .0001), respectively. However, only the 14-mg/day dose significantly reduced disability progression (time to 12-week confirmed disease progression [CDP]: HR, 0.685; P = .044). In addition, both doses of teriflunomide were well tolerated.

came back like this:

The phase 3 TOWER trial compared teriflunomide 7 mg/day and 14 g/day with placebo in patients with MS . The results of this trial showed that, compared with placebo, the 7-mg/day and the 14-mg/day dosages of teriflunomide significantly reduced ARR by 22.3% and 36.3% , respectively. However, only the 14-mg/day dose significantly reduced disability progression . In addition, both doses of teriflunomide were well tolerated.

<tbody>
</tbody>

- Sorry for the boxy formatting -

Is there another to scrub out that stubborn extra space? I suspect the intra-parenthetical brackets may be causing it...

Thank you sooo much!!

 

[mbeyer]

Yes. Or just one Find & Replace with Find what: [space](*)

Assuming the string doesn't begin with a parenthesis of course.

Regards

Thanks for this suggestion XOR LX - I will definitely keep this in mind for future excel endeavors!!

Thanks again!

 

[Rick Rothstein]

This formula worked wonderfully for the above example! However, some text was returned with an extra space that I couldn't eliminate with the trusty TRIM(CLEAN(SUBSTITUTE(U55,CHAR(160)," "))) formula. U55 was the cell that ran your the awesome NoParens formula.

For example, this:
The phase 3 TOWER trial compared teriflunomide 7 mg/day and 14 mg/day with placebo in patients with MS (Kappos L et al. ECTRIMS 2012. Abstract 153. Miller A et al. 65th Annual Meeting of the American Academy of Neurology [AAN 2013]. Abstract S01.004). The results of this trial showed that, compared with placebo, the 7-mg/day and the 14-mg/day dosages of teriflunomide significantly reduced ARR by 22.3% (P = .0183) and 36.3% (P = .0001), respectively. However, only the 14-mg/day dose significantly reduced disability progression (time to 12-week confirmed disease progression [CDP]: HR, 0.685; P = .044). In addition, both doses of teriflunomide were well tolerated.

came back like this:

The phase 3 TOWER trial compared teriflunomide 7 mg/day and 14 g/day with placebo in patients with MS . The results of this trial showed that, compared with placebo, the 7-mg/day and the 14-mg/day dosages of teriflunomide significantly reduced ARR by 22.3% and 36.3% , respectively. However, only the 14-mg/day dose significantly reduced disability progression . In addition, both doses of teriflunomide were well tolerated.

<tbody>
</tbody>

- Sorry for the boxy formatting -

Is there another to scrub out that stubborn extra space? I suspect the intra-parenthetical brackets may be causing it...

Maybe the code I posted here will work for you...

Trim all Cells in a Worksheet - VBA