VBA: Splitting a big string with no good delimiters

[Buddy]

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?

[Paul_Hossler]

There might be some special conditions that need special handling, but try this. It splits the results starting one column over from the input string (can be adjusted)

The sub drv tests data in the attachment

Option Explicit

Sub TennisScores(r As Range)
    Dim c As Range
    Dim s As String, sHold As String
    Dim A As Variant
    Dim i As Long
    
    For Each c In r.Columns(1).Cells
        s = r.Cells(1, 1).Value
    
        sHold = vbNullString
    
        If InStr(s, "[*0-0]") > 0 Then
            sHold = "First"
        ElseIf InStr(s, "[0-*0]") > 0 Then
            sHold = "Second"
        End If
    
        A = Split(s, " ")
        
        For i = LBound(A) To UBound(A)
            If Left(A(i), 1) <> "[" And Right(A(i), 1) <> "]" Then
                sHold = sHold & "," & A(i)
            End If
        Next i
    
        A = Split(sHold, ",")
                
        c.Offset(0, 1).Resize(1, UBound(A) + 1).Value = A
    
    Next
End Sub

Sub drv()
    Call TennisScores(Range("A1:A10"))
End Sub

[Buddy]

Thanks a lot, Paul! It's seems very promising to me!

I don't know if I'm doing something wrong, but the script here is repeating the same data in all rows, even when I use different cells. Below, an example with 3 different cells:

Tennis v1.1.xlsm

By the way, Is there a way to repeat the operation until the last fullfilled row automatically? Without I'm having to editing the range of rows in the script? I tried to set the Range as "A:A", but the Excel froze to death.

Thanks for your patience!

[Buddy]

Also, I need to get the service indication from the first real point (like *15-0, 15-0*, *0-15 or 0-15*) because the * on 0-0 doesn't reflect the reality.

So, I changed this part of the script:

If InStr(s, "[*15-0]") > 0 Then
            sHold = "First"
        ElseIf InStr(s, "[*0-15]") > 0 Then
            sHold = "First"
        ElseIf InStr(s, "[15-*0]") > 0 Then
            sHold = "Second"
        ElseIf InStr(s, "[0-*15]") > 0 Then
            sHold = "Second"
        End If

Is it right?

[Paul_Hossler]

1. this will do A1 to the end of col A data or the first blank

2. Added your 'first real point' logic

3. I think the data looked the same on the first because I just copied your example 10 times

Option Explicit

Sub TennisScores_1()
    Dim c As Range
    Dim s As String, sHold As String
    Dim A As Variant
    Dim i As Long
    
    For Each c In Range(Range("A1"), Range("A1").End(xlDown)).Cells
        s = c.Value
    
        sHold = vbNullString
    
        A = Split(s, " ")
        
        Select Case A(2)
            Case "[*15-0]", "[*0-15]"
                sHold = "First"
            Case "[15-*0]", "[0-*15]"
                sHold = "Second"
        End Select
        
        For i = LBound(A) To UBound(A)
            If Left(A(i), 1) <> "[" And Right(A(i), 1) <> "]" Then
                sHold = sHold & "," & A(i)
            End If
        Next I
    
        A = Split(sHold, ",")
                
        c.Offset(0, 1).Resize(1, UBound(A) + 1).Value = A
    
    Next
End Sub

[snb]

Sub M_snb()
  sn = Cells(1).CurrentRegion
 
  For j = 1 To UBound(sn)
     sp = Filter(Split(Replace(Replace(Replace(sn(j, 1), "] [", "_"), "] ", "_|"), " [", "_"), "_"), "|")
     Cells(1, 10 + j).Resize(UBound(sp) + 1) = Application.Transpose(sp)
   Next
End Sub

[Buddy]

Very nice, Paul!

I just added a Select Case A(3) because sometimes there is a third [0-0] before the [0-15] or the [15-0]. But it seems it's working fine.
Let me test a little bit more to not doing precipitated new questions.

And thank you too, snb!

It seems a interesting approach. I will try understand better what you did and I give you a feedback.

Deeply grateful!