Help Analyzing Baseball Linescore Strings In Excel

[LunaticFringe82]

As part of my analysis of major league baseball "game logs", I need to break down linescore strings, which vary in length.

The strings are presented as follows:

"100230000" - Means that the team scored one in the 1st inning, two in the 4th, and three more in the 5th. (If they were all like this, I wouldn't be posting here!)

"2001" - Means that the team scored two runs in the 6th and one more in the 9th (assuming that the game lasted nine innings, which can be determined elsewhere). Instead of leading zeros (to signify no runs scored in any of the first five innings), they just shorten the string.

"2001x" - Means that the team scored two in the 5th, one in the 8th, and then did not bat in the 9th because they are the home team and still led after the visiting team batted in their half of the 9th. Again, this assumes a nine inning game.

"1003(12)0011" - Means that the team scored one in the 1st, three in the 4th, TWELVE in the 5th, then one each in the 8th and 9th.

My goal is to break these into a grid that for every game provides a column containing the 1st inning value, 2nd inning value, etc.

I've been wracking my small brain (which doesn't know macros) with no success and hope that some of the larger brains could help me with this.

Thanks!

 

[Barrie Davidson]

Welcome to MrExcel!

"2001" - Means that the team scored two runs in the 6th and one more in the 9th (assuming that the game lasted nine innings, which can be determined elsewhere). Instead of leading zeros (to signify no runs scored in any of the first five innings), they just shorten the string.

How do you know that the first digit is the 6th inning and not another one? (for example, say it was scored in the 9th inning, went to extra innings and scored one in 12th inning)

 

[LunaticFringe82]

Thanks for the reply!

You're right, but....

There is another field in the database where the number of innings that the game lasted is found. That is another consideration in this analysis, but I figure that will the a relatively easy step once I can get everything else separated properly.

 

[Barrie Davidson]

I've written the code below using the following assumptions:
• Number of innings is referenced in the column to the right of your box scores.
• Each inning will be extracted in the columns beginning to the right of the cell referencing the number of innings.

My tests had the box scores in column A, number of innings in column B, and I put the scores in columns C to K (for nine inning games).

Option Explicit
Sub ExtractBoxScores()
'Written by Barrie Davidson
Dim Innings As Integer
Dim i As Integer
Dim ScoreLessInnings As Integer
Dim BoxScore As String
Dim BigScore As Integer

Innings = ActiveCell.Offset(, 1)
BigScore = Len(ActiveCell) - Len(Application.WorksheetFunction.Substitute(ActiveCell, "(", ""))
ScoreLessInnings = Innings - (Len(ActiveCell) - BigScore * 3)
BoxScore = CStr(ActiveCell)
For i = 1 To Innings
    If i <= ScoreLessInnings Then
        ActiveCell.Offset(, i + 1) = 0
    Else
        If Left(BoxScore, 1) = "(" Then
            ActiveCell.Offset(, i + 1) = Mid(BoxScore, 2, 2)
            BoxScore = Right(BoxScore, Len(BoxScore) - 4)
        Else
            ActiveCell.Offset(, i + 1) = Left(BoxScore, 1)
            BoxScore = Right(BoxScore, Len(BoxScore) - 1)
        End If
    End If
Next i

End Sub

Select a cell with the box score (column A for my testing) and run the macro. Does this help you out?