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:

20832

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!