Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String help
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 -- Ramthebuff ----------------------------------------------------------------------- Ramthebuffs's Profile: http://www.excelforum.com/member.php...fo&userid=1642 View this thread: http://www.excelforum.com/showthread.php?threadid=52284 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String help
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,:P) k = k + 1 ' add one to the number of sets of runs End If Next i Range("a1").Value = CountOfHomers End Su ------------------- g -- irishboy ----------------------------------------------------------------------- irishboyx's Profile: http://www.excelforum.com/member.php...fo&userid=3249 View this thread: http://www.excelforum.com/showthread.php?threadid=52284 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String help
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
String help
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
String help
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:"®EX.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Create a formula into a String then assign string to a cell | Excel Programming |