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!

 

[mbeyer]

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

Trim all Cells in a Worksheet - VBA

Thanks for the suggestion, Mr. Rothstein - unfortunately the spaces persist. I love would to hear any other ideas you might have.

Thank you so much!
Michael

 

[Rick Rothstein]

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

Trim all Cells in a Worksheet - VBA

Ignore the above (well, you can take note of it, but it has nothing to do with the extra spaces you see)... those extra spaces are normal space characters and result from your closing parentheses resting next to a punctuation mark (actually, the resulting space is the one in front of the opening parenthesis)... the Application.Trim function call does nothing to remove them. Here is my NoParens function revised to handle this problem...

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

 

[mbeyer]

Ignore the above (well, you can take note of it, but it has nothing to do with the extra spaces you see)... those extra spaces are normal space characters and result from your closing parentheses resting next to a punctuation mark (actually, the resulting space is the one in front of the opening parenthesis)... the Application.Trim function call does nothing to remove them. Here is my NoParens function revised to handle this problem...

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

Hello again, Mr. Rothstein -

The new UDF still didn't scrub those extra spaces - perhaps I am doing something wrong?

Thanks so much for your time!

 

[Rick Rothstein]

Hello again, Mr. Rothstein -

The new UDF still didn't scrub those extra spaces - perhaps I am doing something wrong?

Can you send a copy of your workbook to me so that I can see what my code is doing with your data directly? If so, my email address is...

rick DOT news AT verizon DOT net

Please mention this thread's title in your message so I can relate what you send me to the discussion here.

 

[mbeyer]

Can you send a copy of your workbook to me so that I can see what my code is doing with your data directly? If so, my email address is...

rick DOT news AT verizon DOT net

Please mention this thread's title in your message so I can relate what you send me to the discussion here.

Certainly! Just sent it now.

Thank you!

 

[Rick Rothstein]

My previous fix turned out to be "silly code" on closer examination. I think this UDF should work better...

Function NoParens(S As String) As String
  Dim X As Long, Parts() As String
  Const Punctuations As String = """.,:;!?"
  Parts = Split(Replace(S, ")", "("), "(")
  For X = 1 To UBound(Parts) Step 2
    Parts(X) = ""
  Next
  NoParens = Application.Trim(Join(Parts, " "))
  For X = 1 To Len(Punctuations)
    NoParens = Replace(NoParens, " " & Mid(Punctuations, X, 1), Mid(Punctuations, X, 1))
  Next
End Function

Note: I guessed at all the types of punctuations that could follow one of your closing parenthesis characters and encapsulated them in the Punctuations constant (the Const statement)... if you find I have missed any, you can just add it to the back of the list (just be make sure it does not naturally occur in your text with a space before it as that is what I used to identify what needs to be fixed).

 

[mbeyer]

It worked!!! I really appreciate you taking time to help me with this and to provide clear, detailed instructions - I can't thank you enough!!!

Have a great day!