Buddy
11-01-2017, 05:54 AM
Dear all,
I need to split a large string from a single cell, with no good delimiters. It's a 'point-by-point' date from a tennis match, exported directly to an Excel workbook from a third-party software.
Unfortunattely, I do not know the VBA language enough to solve this by my own, and I could not find a similar example here in the forum. So, can some blessed soul help me, please?
This is an example of the content of my A1 cell:
0-0 [*0-0] [0-15*] [15-15*] [15-30*] [30-30*] [40-30*] [40-40*] [40-A*] [40-40*] [A-40*] 1-0 [*0-0] [*0-15] [*15-15] [*15-30] [*30-30] [*40-30] 2-0 [0-0*] [15-0*] [30-0*] [30-15*] [40-15*] 3-0 [*0-0] [*0-15] [*15-15] [*30-15] [*40-15] 4-0 [0-0*] [15-0*] [30-0*] [40-0*] 5-0 [*0-0] [*15-0] [*15-15] [*30-15] [*40-15] 6-0 0-0 [0-0*] [0-15*] [0-30*] [0-40*] 6-0 0-1 [*0-0] [*0-15] [*15-15] [*15-30] [*30-30] [*30-40] [*40-40] [*A-40] 6-0 1-1 [0-0*] [0-15*] [15-15*] [30-15*] [30-30*] [40-30*] 6-0 2-1 [*0-0] [*15-0] [*15-15] [*15-30] [*30-30] [*40-30] [*40-40] [*A-40] [*40-40] [*A-40] [*40-40] [*40-A] 6-0 2-2 [0-0*] [0-15*] [0-30*] [15-30*] [15-40*] 6-0 2-3 [*0-0] [*0-15] [*0-30] [*0-40] 6-0 2-4 [0-0*] [0-15*] [0-30*] [0-40*] 6-0 2-5 [*0-0] [*15-0] [*30-0] [*30-15] [*40-15] 6-0 3-5 [0-0*] [0-15*] [0-30*] [15-30*] [30-30*] [40-30*] 6-0 4-5 [*0-0] [*15-0] [*30-0] [*40-0] 6-0 5-5 [0-0*] [0-15*] [15-15*] [30-15*] [30-30*] [30-40*] [40-40*] [40-A*] 6-0 5-6 [*0-0] [*15-0] [*30-0] [*30-15] [*40-15] [*40-30] 6-0 6-6 [0-0*] [*1-0] [*2-0] [2-1*] [3-1*] [*4-1] [*5-1] [6-1*] 6-0 7-6(1)
The * indicates who is serving
The numbers inside the brackets are the points inside each game or in a tiebreak
The numbers outside the brackets are the final score of each game
After the end of the first set (6-X or 7-5), the numbers outside the brackets include the previously set scores
Important: The first characters, before the first real point [0-15*], are useless, IMO. First, because the indication of who is serving is usually wrong (like in this example); Second, because sometimes the string starts a little different, without the first "0-0" or with some other useless zeros, like "0-0 [0-0] [* 0-0]".
That said, what I need extract from this data are only two things:
A column saying who served in the first game (left player or right player)
The sequence only of the games scores (wihout the point-by-point) in diferent columns
Like this:
1-0 | 1-1 | 2-1 | 3-1 | 4-1 ...
I already did this using Excel formulas, but I needed dozens of new columns, each one with big inefficient formulas, what is making it impossible to process in Excel.
Is there a easiest way to do this using VBA?
I need to split a large string from a single cell, with no good delimiters. It's a 'point-by-point' date from a tennis match, exported directly to an Excel workbook from a third-party software.
Unfortunattely, I do not know the VBA language enough to solve this by my own, and I could not find a similar example here in the forum. So, can some blessed soul help me, please?
This is an example of the content of my A1 cell:
0-0 [*0-0] [0-15*] [15-15*] [15-30*] [30-30*] [40-30*] [40-40*] [40-A*] [40-40*] [A-40*] 1-0 [*0-0] [*0-15] [*15-15] [*15-30] [*30-30] [*40-30] 2-0 [0-0*] [15-0*] [30-0*] [30-15*] [40-15*] 3-0 [*0-0] [*0-15] [*15-15] [*30-15] [*40-15] 4-0 [0-0*] [15-0*] [30-0*] [40-0*] 5-0 [*0-0] [*15-0] [*15-15] [*30-15] [*40-15] 6-0 0-0 [0-0*] [0-15*] [0-30*] [0-40*] 6-0 0-1 [*0-0] [*0-15] [*15-15] [*15-30] [*30-30] [*30-40] [*40-40] [*A-40] 6-0 1-1 [0-0*] [0-15*] [15-15*] [30-15*] [30-30*] [40-30*] 6-0 2-1 [*0-0] [*15-0] [*15-15] [*15-30] [*30-30] [*40-30] [*40-40] [*A-40] [*40-40] [*A-40] [*40-40] [*40-A] 6-0 2-2 [0-0*] [0-15*] [0-30*] [15-30*] [15-40*] 6-0 2-3 [*0-0] [*0-15] [*0-30] [*0-40] 6-0 2-4 [0-0*] [0-15*] [0-30*] [0-40*] 6-0 2-5 [*0-0] [*15-0] [*30-0] [*30-15] [*40-15] 6-0 3-5 [0-0*] [0-15*] [0-30*] [15-30*] [30-30*] [40-30*] 6-0 4-5 [*0-0] [*15-0] [*30-0] [*40-0] 6-0 5-5 [0-0*] [0-15*] [15-15*] [30-15*] [30-30*] [30-40*] [40-40*] [40-A*] 6-0 5-6 [*0-0] [*15-0] [*30-0] [*30-15] [*40-15] [*40-30] 6-0 6-6 [0-0*] [*1-0] [*2-0] [2-1*] [3-1*] [*4-1] [*5-1] [6-1*] 6-0 7-6(1)
The * indicates who is serving
The numbers inside the brackets are the points inside each game or in a tiebreak
The numbers outside the brackets are the final score of each game
After the end of the first set (6-X or 7-5), the numbers outside the brackets include the previously set scores
Important: The first characters, before the first real point [0-15*], are useless, IMO. First, because the indication of who is serving is usually wrong (like in this example); Second, because sometimes the string starts a little different, without the first "0-0" or with some other useless zeros, like "0-0 [0-0] [* 0-0]".
That said, what I need extract from this data are only two things:
A column saying who served in the first game (left player or right player)
The sequence only of the games scores (wihout the point-by-point) in diferent columns
Like this:
1-0 | 1-1 | 2-1 | 3-1 | 4-1 ...
I already did this using Excel formulas, but I needed dozens of new columns, each one with big inefficient formulas, what is making it impossible to process in Excel.
Is there a easiest way to do this using VBA?