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.

 

[Mathew23]

I mean I am using this formula:

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)
SumParens = SumParens + Val(Parts(X))
Next
Else
SumParens = ""
End If
End Function

 

[pgc01]

Hi
Welcome to the board

Why a udf? It seems a formula will do it. For ex.:

=SUMPRODUCT(--MID(E2:E4,FIND("(",E2:E4)+1,FIND(")",E2:E4)-FIND("(",E2:E4)-1))

 

[Mathew23]

I tried the formula you have provided above and it gave an error; not sure what to do.

The UDF seems to work for me but only for one row.

Either way will work as long as I can get a sum for multiple rows.

thanks for all your help.

 

[Peter_SSs]

I tried the formula you have provided above and it gave an error;..

1. What error?

2. Do you have any
- blank cells in the range or
- any cells other cells that do not contain a number enclosed between the first opening parenthesis and the first closing parenthesis?


If so, perhaps try this modification.

=SUMPRODUCT(--MID(E2:E4&"(0)",FIND("(",E2:E4&"(")+1,FIND(")",E2:E4&"(0)")-FIND("(",E2:E4&"(")-1))

If this still doesn't work, please give a small set of sample data for which it fails.