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!
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!
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?
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
Cell 1 is what's INSIDE the parens
Cell 2 is what's OUTSIDE the parens
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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
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
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
Cell1 = MID(String,location of open,location of close -location of open)
Cell2 = left(String, location of open) + right(String,length-locati
Cell 1 = = MID(A1,SEARCH("(",A1)+1,SE
Cell 2 =LEFT(A1,SEARCH("(",A1)-1)