Link to home
Start Free TrialLog in
Avatar of LCMSdev
LCMSdev

asked on

Excel VBA: isolate text in parentheses

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!
Avatar of rgautier
rgautier
Flag of United States of America image

Ok, basic format will be

Cell1 = MID(String,location of open,location of close -location of open)
Cell2 = left(String, location of open) + right(String,length-location of close)

Cell 1 = = MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)
Cell 2 =LEFT(A1,SEARCH("(",A1)-1) &RIGHT(A1,LEN(A1)-SEARCH(")",A1))
Avatar of LCMSdev
LCMSdev

ASKER

Just to make sure I understand you correctly, to relate your terms back to my example: A1 = CurrentText, Cell 1 = NoParens, and Cell 2 = InParens, right?
Avatar of LCMSdev

ASKER

Bah, I have that backwards, but you get my meaning.
A1 is current text
Cell 1 is what's INSIDE the parens
Cell 2 is what's OUTSIDE the parens
ASKER CERTIFIED SOLUTION
Avatar of msheskey
msheskey
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In my test Excel file, Cell A1 contained  'Some text (STRINGME)here'

I put the first formula in B1 - which now said 'STRINGME'
I put the second one in B2 - which now said 'Some text here'
msheskey's routine is probably better as it uses VBA for the solution - I was solving it using the worksheet functionality.
Hi,

try this UDF.

for InParens

MsgBox XTRACT("Some Sample Text (SST)", True)

for NoParens

MsgBox XTRACT("Some Sample Text (SST)", False)

Kris
Function XTRACT(ByVal strInput As String, blnInParents As Boolean) As String
    Dim lpPos   As Long
    Dim rpPos   As Long
    
    lpPos = InStr(1, strInput, "(")
    rpPos = InStr(1, strInput, ")")
    
    If lpPos * rpPos Then
        If blnInParents Then
            XTRACT = Mid$(strInput, lpPos + 1, rpPos - lpPos - 1)
        Else
            XTRACT = Trim$(Left$(strInput, lpPos - 1) & Mid$(strInput, rpPos + 1))
        End If
    End If
End Function

Open in new window

Avatar of LCMSdev

ASKER

This is what I was looking for, thanks!
krishnakrkc deserves the points on this, if you look closely at my results after testing both cases in the original question, you will see two spaces when the parenthesis are in the middle of the string, I looked at krishnakrkc's code and modified mine to trim the trailing spaces, I have attached the new code

Sub getSubString()

Dim InParens As String, NoParens As String, total As String
Dim leftParens As Integer, rightParens As Integer

total = "Some Sample Text (SST)"

leftParens = InStr(total, "(")
rightParens = InStr(total, ")")
difference = rightParens - leftParens - 1

InParens = Mid$(total, (leftParens + 1), difference)
NoParens = Trim$(Left$(total, leftParens - 1)) & _
    Right$(total, Len(total) - rightParens)

MsgBox (InParens & vbCrLf & vbCrLf & NoParens)

End Sub

Open in new window