Retrieve value in between parenthesis inside a cell

[Pookiemeister]

As the subject line states, I am wanting to retrieve the value between the open and closed parentheses. This is what I have so far. Thank you.

Option Compare Text
Private Sub Workbook_Open()
    Dim cell As Range
    Dim str As String
    Dim n As Long

    For Each cell In Range("b2:b6")
        str = cell.Value
        n = InStr(str, "(")
        
        If n = 0 Then
            MsgBox "Character not found"
        Else
            cell.Select
            MsgBox "Character found in position: " & n
            MsgBox Left(str, n + 1)
        End If

    Next cell

End Sub

 

[awoohaw]

is it just one set of parenthesis? Do you require a VBA solution, would a formula work?

Mr excel questions 39.xlsm
	F	G
2	2	"A"="B",1,2
Sheet5

Cell Formulas
Range	Formula
F2	F2	=IF("A"="B",1,2)
G2	G2	=TEXTBEFORE(TEXTAFTER(FORMULATEXT(F2),"("),")")

 

[Pookiemeister]

VBA Solution please. Thank you.

 

[Pookiemeister]

VBA Solution please. Thank you.

Yes, all characters within the parenthesis.

 

[awoohaw]

VBA Solution please. Thank you.

My VBA skills are rudimentary. But, you could use the worksheetformula function in VBA to get text before and text after.

 

[Peter_SSs]

is it just one set of parenthesis?

@Pookiemeister
You did not address the above question, but assuming the answer to that is "yes", you could try this.

Sub BetweenParentheses()
  Range("B2:B6").TextToColumns Range("C2"), xlDelimited, , , False, False, False, False, True, ")", Array(Array(1, 1), Array(2, 9))
  Range("C2:C6").TextToColumns , xlDelimited, , , False, False, False, False, True, "(", Array(Array(1, 9), Array(2, 1))
End Sub

Here is my sample data and the result of the above code.

Pookiemeister.xlsm
	B	C
1
2	abc(def)ghi	def
3
4	Cost ($45.73)	$45.73
5	abcdef
6	123456
Sheet1