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.

 

[Aladin Akyurek]

=MID(A1,SEARCH("(",A1)+1,LEN(A1)-SEARCH(")",A1))

 

[gittar29]

I tried putting this in, and it just gives me a blank, no error. I understand what you did, so I will play around and try to get this myself. Thanks for the quick response.

 

[plettieri]

Hi:

Please post up your findings... I am also interested in the results.

Tks

plettieri

 

[Aladin Akyurek]

I tried putting this in, and it just gives me a blank, no error. I understand what you did, so I will play around and try to get this myself. Thanks for the quick response.

That was wrong...

Try...

=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

instead.

 

[gittar29]

While posting this, I just noticed Aladin had responded...but here's my thoughts anyway..


After some serious playing around, thanks to Aladin's first suggestion, I was able to come up with this, which works with any size string.

=MID(A1,(SEARCH("(",A1)+1),((SEARCH(")",A1)-(SEARCH("(",A1)+1))))

So explanations on how this works: Starting with the inner parts

1) I searched for the first parentheses "(", and added one to get to the first real character.

2) Then searched for the last parentheses ")", and took away the position of the first parentheses from this.

3) The MID looks at cell A1, and takes these results to make its argument.

So for example: (All characters and spaces are counted)

(AAA-AAAA)

Open parentheses : Position 1
Closed parentheses: Position 9

So 1+1 = 2 , and 9-2 = 7 so....

MID (A1, 2, 7) gives you AAA-AAAA

Thanks so much for the ideas for this. Hope it helps someone else.

 

[Blackban88s]

How could you sum the values between parentheses when text string has multiple values? Ex: ="text text text (7.8); text text text (1.1); text text text (3.2)"

 

[Rick Rothstein]

How could you sum the values between parentheses when text string has multiple values? Ex: ="text text text (7.8); text text text (1.1); text text text (3.2)"

I'm not sure if that can be done with a formula or not, but here is a UDF (user defined function) that you can use to do it..

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

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SumParens just like it was a built-in Excel function. For example,

=SumParens(A1)

 

[Blackban88s]

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).
For the moment I'm going with this nested formula then summing the results but it's taking a long time to process.

=IFERROR(VALUE(MID(A1,FIND("^",SUBSTITUTE(A1,"(","^",COLUMNS($A1:A1)))+1,FIND("^",SUBSTITUTE(A1,")","^",COLUMNS($A1:A1)))-FIND("^",SUBSTITUTE(A1,"(","^",COLUMNS($A1:A1)))-1)),"")

 

[pgc01]

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).

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

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">text text text (7.8); text text text (1.1); text text text (3.2)</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">12.1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">text (7.8); text (123-456); text (3.2); text (123ABCD)</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">11</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>


Remark: I did not consider thousand separators.