String help

[Ramthebuffs]

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

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

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

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

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

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

 

[Guest]

Hi Ramthebuffs,

Try this to see if it's what you want. It assumes you will manually select
the cells that you want trimmed. You could modify that to reference a range.


Sub TrimString()
' Trims unwanted characters from a text string

Dim iPos1 As Integer, iPos2 As Integer, j As Long
Dim rngTextToTrim As Range

Set rngTextToTrim = Selection

For j = 1 To Selection.Cells.Count
With rngTextToTrim
Do
iPos1 = InStr(1, .Cells(j), " (", vbTextCompare)
If iPos1 = 0 Then Exit Do
iPos2 = InStr(1, .Cells(j).Value, ")", vbTextCompare)
.Cells(j).Value = Left$(.Cells(j).Value, iPos1 - 1) &
Mid$(.Cells(j).Value, iPos2 + 1)
Loop
End With
Next

End Sub

HTH
Regards,
GS

 

[irishboyx]

this should work for you


Code
-------------------
Option Explicit

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim CountOfHomers As Integer
Dim StringOfData As String


Sub SortTheThingys()
CountOfHomers = 0
j = 1
i = 0
k = 0

StringOfData = "Derek Jeter (3, Bonderman), Alfonso ... (6, Roney)"

For i = 1 To Len(StringOfData) ' cycle through each charachter in the string

If Mid(StringOfData, i, 1) = "(" Then ' if you hit a bracket
j = j + 1 ' add one to brackets
If k * 2 < j Then ' if number of <sets> of runs is less than 2 X brackets
CountOfHomers = CountOfHomers + 1 ' add a run on
k = k + 1 ' add one to the number of sets of runs
End If
End If

If Mid(StringOfData, i, 1) = ")" Then j = j + 1 ' add one to number of brackets

If IsNumeric(Mid(StringOfData, i, 1)) = True And j Mod 2 <> 0 Then ' if its a number and brackets are not even
CountOfHomers = CountOfHomers + Mid(StringOfData, i, 1) ' add on that number
'(i dont play baseball but i assumed no player would get more than 9 homeruns
'in one game,)
k = k + 1 ' add one to the number of sets of runs
End If

Next i

Range("a1").Value = CountOfHomers

End Su

 

[JeffMelton]

Thanks a lot, seems to work great. It is counting a single instance as
2, but I'm sure I can go over it when I have a little time and figure
where to fix it.

Thanks again

 

[Guest]

Better solution than my previous post. I didn't catch that you wanted the
final count, but just a string you could work with easier. This does it all.

Sub CountHomeRuns()
' Trims unwanted characters from a delimited HR text string
' Then replaces the string with the HR count in the string

Dim iPos1 As Integer, iPos2 As Integer, iRuns As Integer
Dim j As Long
Dim rngTextToTrim As Range
Dim s As Variant, sHR As Variant

Set rngTextToTrim = Selection

'Remove unwanted data
For j = 1 To rngTextToTrim.Cells.Count
With rngTextToTrim
sHR = .Cells(j).Value
Do
iPos1 = InStr(1, sHR, " (", vbTextCompare)
If iPos1 = 0 Then Exit Do
iPos2 = InStr(1, sHR, ")", vbTextCompare)
sHR = Left$(sHR, iPos1 - 1) & Mid$(sHR, iPos2 + 1)
Loop

'Get the count
iRuns = 0
For Each s In Split(sHR, ",", , vbTextCompare)
If IsNumeric(Right(s, 1)) Then
iRuns = iRuns + CInt(Right(s, 1))
Else
iRuns = iRuns + 1
End If
Next
.Cells(j).Value = iRuns
End With
Next

End Sub

Regards,
GS

 

[Ron Rosenfeld]

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