Extracting the values between parentheses in two ranges of columns

[Mark K_]

Good morning. I am working on a project for a gaming community and am stuck on a particular section. I got the formula I need to extract the value of numbers between parentheses but I am unsure how to apply it to a horizontal range of cells, two ranges to be precise.

Here is the formula I am using to extract the values between parentheses for each column in a range:
MID(CELL#,SEARCH("(",CELL#)+1,SEARCH(")",CELL#)-SEARCH("(",CELL#)-1)

Examples of cell values:
6
x
2 (10)
7 (100)

The first two examples of cell values obviously do not have a number in parenthesis so the formula needs to work(pass) even it doesn't. Those cells that do not have a number in parentheses, could at any time change so that they do.

So here what I am working with:

I hate the formatting in this forum (sorry) so here is a sample screenshot on Flickr:

The two ranges are the columns that may or may not have the number with a parentheses.

Price (col A) = price of item
Qty (col b) = the cell that I need to put this formula in counting the values in parentheses for each cell in range 1 and range 2
Cost (col c) = Price * Qty

So each row in the QTY column would hold a total count of each cell of the two ranges.

So for example, B1 =
MID(D1,SEARCH("(",D1)+1,SEARCH(")",D1)-SEARCH("(",D1)-1) +
MID(E1,SEARCH("(",E1)+1,SEARCH(")",E1)-SEARCH("(",E1)-1) +
MID(F1,SEARCH("(",F1)+1,SEARCH(")",F1)-SEARCH("(",F1)-1) +
MID(G1,SEARCH("(",G1)+1,SEARCH(")",G1)-SEARCH("(",G1)-1) +
MID(H1,SEARCH("(",H1)+1,SEARCH(")",H1)-SEARCH("(",H1)-1) +
MID(I1,SEARCH("(",I1)+1,SEARCH(")",I1)-SEARCH("(",I1)-1) +
MID(J1,SEARCH("(",J1)+1,SEARCH(")",J1)-SEARCH("(",J1)-1) +
MID(K1,SEARCH("(",K1)+1,SEARCH(")",K1)-SEARCH("(",K1)-1) +
MID(L1,SEARCH("(",L1)+1,SEARCH(")",L1)-SEARCH("(",L1)-1)

Then C1 = total count in B1 * Price in A1

The number of columns in each range can change on any given day depending on if the player creates a new character. If it was only a few columns I would just do it as written above, stringing them all along like that with + signs. But every player is different and they each have a different amount of characters per server. So how can I apply this parenthesis formula to each cell in two different ranges and put the final count of it all into one cell of the B column?

I am not very good at formulating arrays which I assume this may need to be. I cannot use macros in my spreadsheet.

The simple solution would be to add an extra column next to each Name column to hold the # in parenthesis but that would double the number of columns obviously and some players have 20-60 characters per account. I have 10 on one account and 3 on another so it isn't so bad for me, but it's troublesome for other players who have many more.

Essentially I want to do:

B1 =
MID(RANGE1,SEARCH("(",RANGE1)+1,SEARCH(")",RANGE1)-SEARCH("(",RANGE1)-1) +
MID(RANGE2,SEARCH("(",RANGE2)+1,SEARCH(")",RANGE2)-SEARCH("(",RANGE2)-1)

Sorry for a long post and thank you in advance.

~ Mark

 

[Mark K_]

I couldn't find an edit button anywhere, anyhow the cells in those two ranges, the ones that are either blank or have an X in them, those can be counted as zero.

 

[Sektor]

Here's UDF which will get number:

Function GetNum$(rng As Range)
    Dim mc
    With CreateObject("VBScript.RegExp")
        .Pattern = "\((\d+)\)": Set mc = .Execute(rng.Value)
        If mc.Count > 0 Then GetNum = mc(0).SubMatches(0)
    End With
End Function

 

[Mark K_]

*sighs, why is there no edit button =) 2am in the morning here so I am messing up, sorry.

I meant to say in my 1st reply to original post that if the cells do not contain a number in parentheses they can be counted as zero, the only tally I want the QTY column to keep is a tally of any numbers in parentheses.

 

[Mark K_]

I'm sorry but I already said in my original post that I cannot use macros.

 

[Mark K_]

Here's UDF which will get number:

Function GetNum$(rng As Range)
    Dim mc
    With CreateObject("VBScript.RegExp")
        .Pattern = "\((\d+)\)": Set mc = .Execute(rng.Value)
        If mc.Count > 0 Then GetNum = mc(0).SubMatches(0)
    End With
End Function

I meant to reply with quote, sorry. I only visit here I hardly ever post so the
way this forum works is different than what I am used to.

I already said in my original post that I cannot use macros. People don't like getting that warning about macros when they download spreadsheets online, it makes them nervous (me too).

 

[Mark K_]

Hello again.

So I was wondering if someone else can take a look at this and see if there is a way I can do this without using macros?

Thanks.