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 ?

 

[NMa91]

Few exceptions are there but check if it is useful:


A B C D
1 Home Player Away Player Games Score 1 5 9 13 17
2 A X 7-1111-411-611-7 1-0 =MID($D3,F$2,4) =MID($D3,G$2,4) =MID($D3,H$2,4) =MID($D3,I$2,4) =MID($D3,J$2,5)
3 A Y 11-811-611-6 1-0 =MID($D4,F$2,4) =MID($D4,G$2,4) =MID($D4,H$2,4) =MID($D4,I$2,4) =MID($D4,J$2,5)
4 A Z 11-711-86-117-1112-10 1-0 =MID($D5,F$2,4) =MID($D5,G$2,4) =MID($D5,H$2,4) =MID($D5,I$2,4) =MID($D5,J$2,5)
5 B X 9-1111-67-116-11 0-1 =MID($D6,F$2,4) =MID($D6,G$2,4) =MID($D6,H$2,4) =MID($D6,I$2,4) =MID($D6,J$2,5)
6 B Y 11-911-811-9 1-0 =MID($D7,F$2,4) =MID($D7,G$2,4) =MID($D7,H$2,4) =MID($D7,I$2,4) =MID($D7,J$2,5)
7 B Z 8-1111-411-74-1110-12 0-1 =MID($D8,F$2,4) =MID($D8,G$2,4) =MID($D8,H$2,4) =MID($D8,I$2,4) =MID($D8,J$2,5)
8 C X 11-1311-76-119-11 0-1 =MID($D9,F$2,4) =MID($D9,G$2,4) =MID($D9,H$2,4) =MID($D9,I$2,4) =MID($D9,J$2,5)
9 C Y 7-116-1111-313-119-11 0-1 =MID($D10,F$2,4) =MID($D10,G$2,4) =MID($D10,H$2,4) =MID($D10,I$2,4) =MID($D10,J$2,5)
10 C Z 8-115-1111-82-11 0-1 =MID($D11,F$2,4) =MID($D11,G$2,4) =MID($D11,H$2,4) =MID($D11,I$2,4) =MID($D11,J$2,5)
11 AC XZ 13-1511-48-1111-910-12 0-1 =MID($D12,F$2,4) =MID($D12,G$2,4) =MID($D12,H$2,4) =MID($D12,I$2,4) =MID($D12,J$2,5)

 

[AlanY]

thanks, didn't work.

the few exceptions are the problem, from your formula

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Home	Player			1	5	9	13	17
2	A	X	7-1111-411-611-7	1-0	7-11	11-4	11-6	11-7
3	A	Y	11-811-611-6	1-0	11-8	11-6	11-6
4	A	Z	11-711-86-117-1112-10	1-0	11-7	11-8	6-11	7-11	12-10
5	B	X	9-1111-67-116-11	0-1	9-11	11-6	7-11	6-11
6	B	Y	11-911-811-9	1-0	11-9	11-8	11-9
7	B	Z	8-1111-411-74-1110-12	0-1	8-11	11-4	11-7	4-11	10-12
8	C	X	11-1311-76-119-11	0-1	11-1	311-	76-1	19-1	1
9	C	Y	7-116-1111-313-119-11	0-1	7-11	6-11	11-3	13-1	19-11
10	C	Z	8-115-1111-82-11	0-1	8-11	5-11	11-8	2-11
11	AC	XZ	13-1511-48-1111-910-12	0-1	13-1	511-	48-1	111-	910-1
Sheet6

 

[JackDanIce]

n/a

 

[FormR]

I’ve downloaded from the league

Hi, are you sure there are no special non-printing characters that are between the scores?

You could check with =LEN(C2) to see if it matches the count of the visible characters.

 

[AlanY]

i wonder that as well

this is the link of a typical result

https://tabletennis365.com/BracknellAndWokingham/Results/Season_2017-18/Premier/MatchCard/230962

even they look to have spaces between them but when I paste them into excel they are joined up
may be i'm not copy and paste them correctly

 

[FormR]

even they look to have spaces between them but when I paste them into excel they are joined up

Same for me

 

[AlanY]

Same for me

thanks for confirming that.
anyone got any suggestions?

 

[NMa91]

Place below code in module. It worked for me. Set no is 1 to 5.

e.g. =SetValue(D2,1) where D2 is cell address for Games D column and set no 1.

Public Function SetValue(Text As String, SetNo As Integer) As String
Dim a() As String
Dim s1 As String, s2 As String

a = Split(Text, "-")

SetValue = ""

Select Case SetNo
    Case 1
        s1 = a(0)
        Select Case Len(a(1))
            Case 4: s2 = Left(a(1), 2)
            Case 3: If Left(a(1), 2) > 20 Then s2 = Left(a(1), 1) Else s2 = Left(a(1), 2)
        End Select
    Case 2
         Select Case Len(a(1))
            Case 4: s1 = Right(a(1), 2)
            Case 3: If Left(a(1), 2) > 20 Then s1 = Right(a(1), 2) Else s1 = Right(a(1), 1)
            Case 2: s1 = Right(a(1), 1)
         End Select
        
         Select Case Len(a(2))
            Case 4: s2 = Left(a(2), 2)
            Case 3: If Left(a(2), 2) > 20 Then s2 = Left(a(2), 1) Else s2 = Left(a(2), 2)
            Case 2: s2 = Left(a(2), 1)
         End Select
    
    Case 3
        Select Case Len(a(2))
            Case 4: s1 = Right(a(2), 2)
            Case 3: If Left(a(2), 2) > 20 Then s1 = Right(a(2), 2) Else s1 = Right(a(2), 1)
            Case 2: s1 = Right(a(2), 1)
         End Select
        
         Select Case Len(a(3))
            Case 4: s2 = Left(a(3), 2)
            Case 3: If Left(a(3), 2) > 20 Then s2 = Left(a(3), 1) Else s2 = Left(a(3), 2)
            Case 2: If UBound(a) = 3 Then s2 = Right(a(3), 2) Else s2 = Left(a(3), 1)
            Case 1: s2 = a(3)
         End Select
    
    Case 4
        If UBound(a) < 4 Then GoTo Result
        
        Select Case Len(a(3))
            Case 4: s1 = Right(a(3), 2)
            Case 3: If Left(a(3), 2) > 20 Then s1 = Right(a(3), 2) Else s1 = Right(a(3), 1)
            Case 2: s1 = Right(a(3), 1)
         End Select
        
         Select Case Len(a(4))
            Case 4: s2 = Left(a(4), 2)
            Case 3: If Left(a(4), 2) > 20 Then s2 = Left(a(4), 1) Else s2 = Left(a(4), 2)
            Case 2: If UBound(a) = 4 Then s2 = Right(a(4), 2) Else s2 = Left(a(4), 1)
            Case 1: s2 = a(4)
         End Select
    
    Case 5
        If UBound(a) < 5 Then GoTo Result
        
        Select Case Len(a(4))
            Case 4: s1 = Right(a(4), 2)
            Case 3: If Left(a(4), 2) > 20 Then s1 = Right(a(4), 2) Else s1 = Right(a(4), 1)
            Case 2: s1 = Right(a(4), 1)
         End Select
        
         Select Case Len(a(5))
            Case 4: s2 = Left(a(5), 2)
            Case 3: If Left(a(5), 2) > 20 Then s2 = Left(a(5), 1) Else s2 = Left(a(5), 2)
            Case 2: If UBound(a) = 5 Then s2 = Right(a(5), 2) Else s2 = Left(a(5), 1)
            Case 1: s2 = a(5)
         End Select

End Select

Result:
SetValue = s1 & "-" & s2

End Function

 

[AlanY]

tested on a few results and it works

Great work, much appreciated.