On Wed, 15 Mar 2006 15:20:44 -0600, Ramthebuffs
wrote:


I've made a query to get some info off a webpage and have come to a
problem I'm not exactly sure how to get what I want. What I'm doing is
getting baseball box scores from websites and then organising the data
into my own little database. My problem is that some of the stats are
presented in a string format. Heres an example of what is in the cell
after the query

HR - Derek Jeter (3, Bonderman), Alfonso Soriano 2 (17, Sparks), Jorge
Posada (13, Sparks), Todd Zeile (6, Roney)

What I was thinking of doing is counting the "(" as that represents a
home run. The problem is when it comes to Alfonso Soriano, the 2 after
his name means he hit 2. The part that says (17, Sparks) simply states
his total homerun count for the season and the pitcher who threw the
HR, both are of no use to what I'm doing. In the above example there
are 5 home runs, which is the result I am wanting.

It can get a little more complicated because it could read something
like this

HR - Derek Jeter (3, Bonderman), Alfonso Soriano 2 (17, Sparks), Jorge
Posada 3 (13, Sparks), Todd Zeile (6, Roney)

where multiple players hit multiple home runs. This example has 7 home
runs. Anybody know how I can get what I need here?

You could download and install Longre's free morefunc.xll add-in, and then use
Regular Expressions to get the information you wish. The add-in is available
at


and the **array** formula:

=SUM(REGEX.COUNT(A1,"(?<!\d\s)\("),--REGEX.MID(A1,
"\d+(?=\s\()",ROW(INDIRECT("1:"&REGEX.COUNT(A1,"\d \s\(")))))

should give you the correct answer.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

Algorithm:

1. Count all "(" that are not preceded by a "<digit<space"
2. Add all numbers that are followed by a "<space<("

Sum 1 and 2.


--ron