# Help Analyzing Baseball Linescore Strings In Excel

#### LunaticFringe82

##### New Member
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!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Barrie Davidson

##### MrExcel MVP
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

##### New Member

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

##### MrExcel MVP
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).

Code:
``````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?

Replies
43
Views
2K
Replies
7
Views
840
Replies
6
Views
522
Replies
2
Views
254
Replies
4
Views
646