Get the value between the brackets

Question

I have a column with some stuff that looks like the following string: V2397(+60)

How do I get the value between the brackets? In this case the +60.

The number (and character) before the brackets is not something standardized and neither the number between the brackets (it can be 100, 10 -10 or even 0...).

Expanded answer at [Get the value between the parentheses, multiple matches in one string](http://stackoverflow.com/questions/34750701/get-the-value-between-the-parentheses-multiple-matches-in-one-string/34751380#34751380). — , Jan 12 '16 at 18:42

Andrew Cooper · Accepted Answer · 2011-11-08T22:43:55.997

13

VBA code:

cellValue = "V2397(+60)"
openingParen = instr(cellValue, "(")
closingParen = instr(cellValue, ")")
enclosedValue = mid(cellValue, openingParen+1, closingParen-openingParen-1)

Obviously cellValue should be read from the cell.

Alternatively, if cell A1 has one of these values, then the following formula can be used to extrcat the enclosed value to a different cell:

=Mid(A1, Find("(", A1)+1, Find(")",A1)-Find("(",A1)-1)

edited Nov 08 '11 at 22:43

answered Nov 08 '11 at 22:38

Andrew Cooper

32,176
5
81
116

1

+1 for showing both the VBA and the formula way of doing things – Jean-François Corbett Nov 09 '11 at 09:31
Thanks a lot for this answer. Appreciated! – Sandrocottus Jan 21 '21 at 13:39

brettdj · Answer 2 · 2011-11-09T07:09:18.387

I would use a regular expression for this as it easily handles

a no match case
multiple matches in one string if required
more complex matches if your parsing needs evolve

The Test sub runs three sample string tests

The code below uses a UDF which you could call directly in Excel as well, ie = GetParen(A10)

Function GetParen(strIn As String) As String
    Dim objRegex As Object
    Dim objRegMC As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Pattern = "\((.+?)\)"
        If .Test(strIn) Then
            Set objRegMC = .Execute(strIn)
            GetParen = objRegMC(0).submatches(0)
        Else
            GetParen = "No match"
        End If
    End With
    Set objRegex = Nothing
End Function

Sub Test()
MsgBox GetParen("V2397(+60)")
MsgBox GetParen("Not me")
MsgBox GetParen(ActiveSheet.Range("A1"))
End Sub

score 1 · Answer 3 · answered Jun 19 '17 at 00:58

Thanks to Andrew Cooper for his answer.

For anyone interested I refactored into a function...

 Private Function GetEnclosedValue(query As String, openingParen As String, closingParen As String) As String
    Dim pos1 As Long
    Dim pos2 As Long

    pos1 = InStr(query, openingParen)
    pos2 = InStr(query, closingParen)

    GetEnclosedValue = Mid(query, (pos1 + 1), (pos2 - pos1) - 1)
End Function

To use

value = GetEnclosedValue("V2397(+60)", "(", ")" )

score 1 · Answer 4 · answered Nov 08 '11 at 22:40

1

Use InStr to get the index of the open bracket character and of the close bracket character; then use Mid to retrieve the desired substring.

Using InStr$ and Mid$ will perform better, if the parameters are not variants.

answered Nov 08 '11 at 22:40

phoog

42,068
6
79
117

Get the value between the brackets

4 Answers4

Linked