How to parse a baseball box score

I am working on a project with baseball data from retrosheet.org. I want to create variables for the score of each team in each inning (VisInning1, HomeInning1, VisInning2, HomeInning2, etc). The problem is that the variable for the box score is coded strangely. Each team has its own variable for the whole game and each inning gets one value. Because leading zeros are cut off, a value of "12(10)1X" would mean that a team did not score in the first 4 innings, scored once in the fifth, twice in the sixth, ten times in the seventh, once in the eighth, and they did not have to play the ninth because they had won by that point. There is a variable for the total number of outs, so I can calculate whether or not there are extra innings.

This is part of a project that I am doing as a gift for my father's 64th birthday in August. He's a huge baseball fan but likes leaving games early to beat the traffic. I'm writing an analysis of the likelihood of a leader change under different score conditions. I'm a grad student and I know the quantitative part, but I am weak with string parsing.

I haven't been able to do anything with this so far. I tried it in R and Java to no avail so this is my first exploration into whether or not I could do it in Excel. I can post a sample of the data if it would help.

I cross-posted my question. I assume there are many different ways to do this so multiple opinions are welcome. I also plan to submit the answer to the people who own the dataset so they can post an FAQ for other people who run into this problem. I've found other people looking for advice on this issue in a number of places but no straightforward answer.

Thank you so much! This is fantastic! Is there any way for it to do something with games over 9 innings? It would be even more useful to have it read the number of outs and calculate innings up to 9 (extra innings don't matter to me).

First off, your formula worked really well for over 140,000 baseball games out of 200,000 so thank you! I have made tremendous progress but still have trouble with games that went into overtime.

There are three outs in one half-inning of baseball. That means that three different people must be removed from play before the game moves onto the next segment. If the home team is leading at the end of the game, they don't bother playing the last half inning. Conversely, if the game is tied after the standard 9 innings, teams continue playing until there is a clear winner. There are normally no ties in baseball. If this is not clear I can explain it in a different way.

For the project I have a column for number of outs and can create one for number of innings. I don't know how to parse the line score based on the number of outs for games lasting longer than 9 innings. I honestly don't care about the score past 9 innings but I at least want to know the score for each of the first 9 innings.

Any ideas?