Split up a linescore

[mhenk]

I have baseball linescores saved as text entries in my data. What I would like to do is break up these linescores (currently single cells) into a cell for each inning. The data is formatted like this:

14000(10)21x, which yields:

1 run in the first, 4 in the second, 0 in the third-fifth, 10 in the sixth, 2 in the seventh, 1 in the eighth, and did not have to play the ninth.

I would like the following 9 cells to be created:

1, 4, 0, 0, 0, 10, 2, 1 ,x.

If you haven't guessed by now, my issue is when there are more than 9 runs in an inning.

VBA is an option, a formula is preferable.

I'll have do something to take into account the games that have more than 9 innings, but I need to resolve this issue first.

 

[mhenk]

Solved.

Thanks.

 

[HalfAce]

Hello mhenk,
Out of curiosity, how did you solve this?
(Particularly, the double digit runs in one inning?)

 

[mhenk]

well, i decided to use intermediate columns, then convert any 10+ run innings to a character code, which I then translated back into an integer once the string was split up.

I did this 8 times (the most runs in an inning was 17, btw).

I started with the raw data entry, for example 14000(10)21x, as above.

I used the following: =SUBSTITUTE(D24,"("&F22&")",F23), where F22 = 10, F23 = A, and D24 is the raw data entry. This replaces all occurrences of (10) with A. I did a similar thing for 11-17, replacing them with characters.

Finanlly, I used a series of MID functions to break apart the line score, putting each character in it's own column. I ran a vlookup to convert any letter back into it's corresponding number.

Not efficient, but it works, and I only had to add about 20 columns, which is no problem, I've got over 1000 to work with! :D

 

[HalfAce]

but it works. . .

In the end that's all that really counts.
I imagine that took a bit of head scratchin' but (considering the quote above) nice solution!

Thanks for sharing.