Another challenging numbers extraction

[AlanY]

This is a typical results for a table tennis league match that I’ve downloaded from the league and would like to do some analysis.

Excel 2013/2016
	A	B	C	D
1	Home Player	Away Player	Games	Score
2	A	X	7-1111-411-611-7	1-0
3	A	Y	11-811-611-6	1-0
4	A	Z	11-711-86-117-1112-10	1-0
5	B	X	9-1111-67-116-11	0-1
6	B	Y	11-911-811-9	1-0
7	B	Z	8-1111-411-74-1110-12	0-1
8	C	X	11-1311-76-119-11	0-1
9	C	Y	7-116-1111-313-119-11	0-1
10	C	Z	8-115-1111-82-11	0-1
11	AC	XZ	13-1511-48-1111-910-12	0-1
Sheet5

The results in Column-C are what I’m interested. The match is for best of 5 games (i.e. between 3-5 games) and each game to 11 points or 2 clear points if the score reached 10-10. E.g. the match between B & Z in Row 7 is 8-11, 11-4, 11-7, 4-11 and 10-12, Z won by 3-2.

The problem as you can see from the table is the score are all joined together with no spaces between them!
Any ideas as how to separate them would be much appreciated

From 7-1111-411-611-7 to 7-11 11-4 11-6 11-7 ?

 

[Peter_SSs]

tested on a few results and it works

I haven't yet thought of a fool-proof alternative, but by my reckoning that function fails for this set score

13-1119-211-114-1113-11

where it produces
13-11, 19-2, 11-11, 4-11, 13-11


instead of
13-11, 19-21, 1-11, 4-11, 13-11

 

[JackDanIce]

Initially, I thought of using Len(2) if after "-" or if the first number is 1, but there doesn't seem to be a catch all solution, especially for triplets of numbers.

Curious to what final solution is, I think it's too ambiguous with current information to clearly define a rule set.

 

[Peter_SSs]

I think it's too ambiguous with current information to clearly define a rule set.

Well, I figured that I could do it manually so there should be a way. This 2-pass method is the best I could come up with.

Sub GetScores()
  Dim a As Variant, bits As Variant
  Dim i As Long, j As Long
  Dim s As String
  
  a = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 5) As String
  For i = 1 To UBound(a)
    j = 0
    s = a(i, 1)
    Do While Len(s) > 0
      j = j + 1
      b(i, j) = Left(s, InStr(1, s, "-") + 2)
      s = Replace(s, b(i, j), "", 1, 1)
      If Left(s, 1) = "-" Then
        s = Right(b(i, j), 1) & s
        b(i, j) = Left(b(i, j), Len(b(i, j)) - 1)
      End If
    Loop
  Next i
  For i = 1 To UBound(a)
    For j = 1 To 5
      bits = Split(b(i, j), "-")
      If UBound(bits) = -1 Then Exit For
      Select Case False
        Case (bits(0) = 11 And bits(1) < 10) Or (bits(1) = 11 And bits(0) < 10) Or (Abs(bits(0) - bits(1)) = 2)
          If bits(0) < 11 Then
            b(i, j) = Right(b(i, j - 1), 1) & b(i, j)
            b(i, j - 1) = Left(b(i, j - 1), Len(b(i, j - 1)) - 1)
          End If
      End Select
    Next j
  Next i
  Range("E2").Resize(UBound(b), 5).Value = b
End Sub

Here is my test data and results

Book1
	C	D	E	F	G	H	I
2	11-1311-76-119-11		11-13	11-7	6-11	9-11
3	11-131-11		11-13	1-11
4	11-126-24		11-1	26-24
5	7-1111-411-611-7		7-11	11-4	11-6	11-7
6	11-811-611-6		11-8	11-6	11-6
7	11-711-86-117-1112-10		11-7	11-8	6-11	7-11	12-10
8	9-1111-67-116-11		9-11	11-6	7-11	6-11
9	11-911-811-9		11-9	11-8	11-9
10	8-1119-211-114-1110-12		8-11	19-21	1-11	4-11	10-12
11	11-1311-76-119-11		11-13	11-7	6-11	9-11
12	7-116-1111-313-119-11		7-11	6-11	11-31	3-11	9-11
13	8-115-1111-82-11		8-11	5-11	11-8	2-11
14	13-1511-48-1111-910-12		13-15	11-4	8-11	11-9	10-12
15	1-1118-2011-131-290-11		1-11	18-20	11-1	31-29	0-11
TT Scores (2)

 

[AlanY]

Well, I figured that I could do it manually so there should be a way. This 2-pass method is the best I could come up with.

Sub GetScores()
  Dim a As Variant, bits As Variant
  Dim i As Long, j As Long
  Dim s As String
  
  a = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 5) As String
  For i = 1 To UBound(a)
    j = 0
    s = a(i, 1)
    Do While Len(s) > 0
      j = j + 1
      b(i, j) = Left(s, InStr(1, s, "-") + 2)
      s = Replace(s, b(i, j), "", 1, 1)
      If Left(s, 1) = "-" Then
        s = Right(b(i, j), 1) & s
        b(i, j) = Left(b(i, j), Len(b(i, j)) - 1)
      End If
    Loop
  Next i
  For i = 1 To UBound(a)
    For j = 1 To 5
      bits = Split(b(i, j), "-")
      If UBound(bits) = -1 Then Exit For
      Select Case False
        Case (bits(0) = 11 And bits(1) < 10) Or (bits(1) = 11 And bits(0) < 10) Or (Abs(bits(0) - bits(1)) = 2)
          If bits(0) < 11 Then
            b(i, j) = Right(b(i, j - 1), 1) & b(i, j)
            b(i, j - 1) = Left(b(i, j - 1), Len(b(i, j - 1)) - 1)
          End If
      End Select
    Next j
  Next i
  Range("E2").Resize(UBound(b), 5).Value = b
End Sub

Here is my test data and results

Book1
	C	D	E	F	G	H	I
2	11-1311-76-119-11		11-13	11-7	6-11	9-11
3	11-131-11		11-13	1-11
4	11-126-24		11-1	26-24
5	7-1111-411-611-7		7-11	11-4	11-6	11-7
6	11-811-611-6		11-8	11-6	11-6
7	11-711-86-117-1112-10		11-7	11-8	6-11	7-11	12-10
8	9-1111-67-116-11		9-11	11-6	7-11	6-11
9	11-911-811-9		11-9	11-8	11-9
10	8-1119-211-114-1110-12		8-11	19-21	1-11	4-11	10-12
11	11-1311-76-119-11		11-13	11-7	6-11	9-11
12	7-116-1111-313-119-11		7-11	6-11	11-31	3-11	9-11
13	8-115-1111-82-11		8-11	5-11	11-8	2-11
14	13-1511-48-1111-910-12		13-15	11-4	8-11	11-9	10-12
15	1-1118-2011-131-290-11		1-11	18-20	11-1	31-29	0-11
TT Scores (2)

many thanks Peter, much appreciated.
i first tried to tackle this problem last year and soon given up, thinking that if only they could put spaces in the results!

but, then I wouldn't be getting these gems out of you guys.
many thanks to everyone again

 

[AlanY]

spoke too soon.

when I copy and run the codes in a module, nothing happened!
have I missed something?

 

[Peter_SSs]

Is your layout like mine with the pasted values from the website in column C like I have?
Was that sheet the active sheet when the code was run?
Do you have columns E:I visible?
How did you run the code?

 

[AlanY]

never mind, just spotted an error.
i started the values in Col D instead of C, it's all working now

cheers