String of Text into Box Score Format (Baseball Data)

Hey,

Need some help with this one. Either formulas or VBA code, doesn't matter which (thinking VBA would be the best option. Am running Excel 2016), but I'll try to explain what I have here:

A1 = "Team Names", B1 = "Box Score Data"
A2 = MILCIN, B2 = 102200322400500600730800910R64H89E01

Let's assume I have a list of these types of strings filling down the A and B columns. What I would like is some code that would essentially transform the above data (in a new sheet if that helps) into something like the below format:

------1st--2nd--3rd--4th--5th--6th--7th--8th--9th--R--H--E
MIL---0----0-----2----0----0----0-----3----0----1----6--8--0
CIN---2----0-----2----0----0----0-----0----0----0----4--9--1

(You can see this data in the string. And obviously the dashes are for readability. These would all be in their own columns)

Sometimes there might be games that go into extra innings, so that string could go past 9 innings, so it would need to understand what the 10th, 11th, etc. innings were. I was thinking of something that could extract everything from the R and to the right, leaving the string for scores and innings left, but I don't know how to transform that. The team names as well. For those, I could have a list of the teams abbreviations, and then have some code that would split the string depending on matching from the list? (If that makes sense). And it would need to loop through the entire list down.

Any ideas? Let me know if I can answer any more questions. Thanks a lot for your help!

windowshopr