Finding value between parentheses

[gittar29]

I am trying to write a string formula which will look a code between parentheses.

My logic is this:

Search for the first parentheses, find the last parentheses, and return everything in between. Im sure there is a string function, but not exactly sure which one will do the trick. And by the way, the code in between the parentheses varies from length 3 to 9.

Thanks.

 

[Rick Rothstein]

Thanks! That UDF works much faster. The only problem I've got with using it is that its also summing up items within parens that aren't actually values ex: (123-456) or (123ABCD).

You should have mentioned that your data could contain non-number is the parentheses so we would have known to design around them. Here is my UDF modified to work with what you have now told us your data looks like...

Function SumParens(S As String) As Variant
  Dim X As Long, Parts() As String
  If Len(S) Then
    Parts = Split(Replace(S, ")", "("), "(")
    For X = 1 To UBound(Parts)
      If Not Parts(X) Like "*[!0-9.]*" And Not Parts(X) Like _
          "*.*.*" And Len(Parts(X)) > 0 And Parts(X) <> "." Then
        SumParens = SumParens + Val(Parts(X))
      End If
    Next
  Else
    SumParens = ""
  End If
End Function

 

[Peter_SSs]

Try this udf:

Function AddP(s As String)

With CreateObject("VBScript.RegExp")
    .Pattern = ".*?\((\d+(\.\d+)?)\)|.*"
    .Global = True
    AddP = Evaluate(Replace(Trim(.Replace(s, "$1 ")), " ", "+"))
End With
End Function

PGC, is this a possible simplification line in your code?

AddP = Evaluate(.Replace(s, "$1+0"))

 

[pgc01]

Hi Peter

Yes it is. And I think it would also work even if this was a problem that had negative numbers ... and it avoids 2 function calls.

Since this is an interesting idea I will use it from now on and say it was my idea.

 

[Peter_SSs]

Since this is an interesting idea I will use it from now on and say it was my idea.

That's not a problem. Plenty of my ideas were your's once too.

 

[Rick Rothstein]

You should have mentioned that your data could contain non-number is the parentheses so we would have known to design around them. Here is my UDF modified to work with what you have now told us your data looks like...

Function SumParens(S As String) As Variant
  Dim X As Long, Parts() As String
  If Len(S) Then
    Parts = Split(Replace(S, ")", "("), "(")
    For X = 1 To UBound(Parts)
      If Not Parts(X) Like "*[!0-9.]*" And Not Parts(X) Like _
          "*.*.*" And Len(Parts(X)) > 0 And Parts(X) <> "." Then
        SumParens = SumParens + Val(Parts(X))
      End If
    Next
  Else
    SumParens = ""
  End If
End Function

Minor correction to my previously posted function...

Function SumParens(S As String) As Variant
  Dim X As Long, Parts() As String
  If Len(S) Then
    Parts = Split(Replace(S, ")", "("), "(")
    For X = 1 To UBound(Parts) Step 2
      If Not Parts(X) Like "*[!0-9.]*" And Not Parts(X) Like _
          "*.*.*" And Len(Parts(X)) > 0 And Parts(X) <> "." Then
        SumParens = SumParens + Val(Parts(X))
      End If
    Next
  Else
    SumParens = ""
  End If
End Function

I had forgotten to skip over the text that was not inside parentheses (which is what the Step 2 that I highlighted in red does for the loop). This would not have been a problem as long as a number, by itself, never appeared outside of parentheses, but why take the chance. Beside, this modification effectively makes the loop portion of my code run nearly twice as fast.

 

[Legacy 173085]

I'm looking for a variation on this formula I don't know if anyone can help...

I need a formula to return the text within the last set of brackets in a cell.

i.e. in some cells there might be 2 or 3 sets of brackets, and I need to return the text within the last set, or at the right side of the cell.

Does anyone know how I might go about this...?

Thanks!

 

[taurean]

Try:
=LEFT(TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",200)),200)),")",REPT(" ",200))),200)

 

[Rick Rothstein]

I'm looking for a variation on this formula I don't know if anyone can help...

I need a formula to return the text within the last set of brackets in a cell.

i.e. in some cells there might be 2 or 3 sets of brackets, and I need to return the text within the last set, or at the right side of the cell.

Does anyone know how I might go about this...?

Assuming by "brackets" you mean what we call parentheses, like this (), and if your text will be less than 100 characters in length, you can use this formula...

=TRIM(LEFT(RIGHT(SUBSTITUTE("("&SUBSTITUTE(A1,")","("),"(",REPT(" ",99)),198),99))

If the length is longer, say less than 300 characters, then this formula would become...

=TRIM(LEFT(RIGHT(SUBSTITUTE("("&SUBSTITUTE(A1,")","("),"(",REPT(" ",300)),600),300))

The more general version of this formula for a text length that is totally unknown would be this...

=TRIM(LEFT(RIGHT(SUBSTITUTE("("&SUBSTITUTE(A1,")","("),"(",REPT(" ",LEN(A1))),2*LEN(A1)),LEN(A1)))

 

[Legacy 173085]

Thank you both, that's perfect! Just what I was looking for! Thank you

 

[Mathew23]

Dear Rick

I am using the formula you posted above; however I have a quick question is there a way to use the UDF function to calculate the values contained within the parenthesis in multiple rows?

For example
Say E2 had: text (2)
F2 had: text (3)
G2 had: text (4)

Can i use the UDf formula above to sum the values within the parenthesis into a single cell; if so how would I go about doing so? thanks for all your help.