VBA: Splitting a big string with no good delimiters

[SantoOnofre]

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] [0-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] [* 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-0 [0-0 *] [0-15 *] [0-30 *] [0-40 *] 6-0 2-5 [* 0-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 *] [30-15 *] [30-30 *] [30-40 *] [40-40 *] [40-A *] 6-0 5-6 [ 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] 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?

 

[SantoOnofre]

PS: There is no space between "[" and "*" ou "*" and "]". It was an error in the pasting process. The correct string is:

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)