As part of a VBA macro I'm writing in Excel 2007, I need to split text values that contain parentheses. Starting with the text in the cell, which I'll call CurrentText, I want to generate two more strings: NoParens and InParens. NoParens should be the text minus any substring that was in parentheses (and adjusted for spacing); InParens should be the text that was contained in the parentheses.
Examples might be helpful. Let's say CurrentText = "Some Sample Text (SST)". In this case, NoParens should be "Some Sample Text", and InParens should be "SST".
Or, if CurrentText = "More Sample Text (MST) here", then NoParens should be "More Sample Text here", and InParens should be "MST".
I can't quite figure this one out, and would be very grateful if anyone could lend a hand. Thanks!