Split up a linescore

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello mhenk,
Out of curiosity, how did you solve this?
(Particularly, the double digit runs in one inning?)
 

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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! :biggrin:

Thanks for sharing.
 

Forum statistics

Threads
1,181,657
Messages
5,931,254
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top