Splitting string

[fredrerik84]

Ive extracted around 100 rows of string where the strings come out like this in excel (in one cell)

"


I dag 17:00




2.200
Tottenham Hotspur




3.600
X




3.700
Chelsea






I dag 14:30




2.850
Huddersfield Town




3.200
X




2.750
Newcastle United
+ 298







I dag 17:00




2.150
Tottenham Hotspur




3.500
X




3.600
Chelsea
+ 283

I tried to splitting it the following way:

For i = 0 To HTMLRows5.Length - 1
        On Error Resume Next
        str = HTMLRows(i).innerText
        Cells(j, "B").value = Trim(Split(HTMLRows(i).innerText, vbCrLf)(2))


        Cells(j, "J").value = Trim(Split(HTMLRows(i).innerText, vbCrLf)(5))
        Cells(j, "E").value = Trim(Split(HTMLRows(i).innerText, vbCrLf)(6))
        Cells(j, "K").value = Trim(Split(HTMLRows(i).innerText, vbCrLf)(9))
        Cells(j, "L").value = Trim(Split(HTMLRows(i).innerText, vbCrLf)(13))
        Cells(j, "G").value = Trim(Split(HTMLRows(i).innerText, vbCrLf)(14))
        j = j + 1
     Next i

Unfortunately for me this will only extract data for one Row

1word goes into cell A (date)
2 word goes to cell E ( teamA)
3 word goes to cell G ( teamB)

4th word goes to cell j
5th word goes to cell k
6th word goes to cell l

Is there a better way to split this string ?
also the length of the string will different from row to row.

Any help with this will be much appreciated

 

[FDibbins]

This wont put the words where you want them, but you should be able to adapt this to your needs...

	A​	B​	C​	D​
2​	I dag 17:00	I	dag	17:00

B1=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN()-2)+1,LEN($A2)))
copied across

 

[fredrerik84]

Hi thanks for your reply I found a solution for this in the end it was way more complicated then I expected , but I made an loop for the words like this:

step4:
   If i = HTMLRows6.lenth Then GoTo step5
   i = i + 1
        On Error Resume Next
        str = HTMLRows(i).innerText
        str2 = HTMLRows6(ii).innerText
        If Len(HTMLRows(i).innerText) = 0 Then GoTo step4
        If InStr(HTMLRows(i).innerText, "X") = 0 Then GoTo step5
           For x = 2 To 200
              If Trim(Split(HTMLRows(i).innerText, vbCrLf)(x)) <> "" Then
                 str = Trim(Split(HTMLRows(i).innerText, vbCrLf)(x))
                 If InStr(str, "+") = 0 Then
                   If s = 0 Then
                       y = 2
                       If InStr(str, "I dag ") <> 0 Then
                          str = Replace(str, "I dag ", "")
                          mDate = Date
                       ElseIf InStr(str, "I mrg ") <> 0 Then
                          mDate = DateAdd("d", 1, mDate)
                          str = Replace(str, "I mrg ", "")
                       End If
                       hourdate = str
                       str2 = Trim(Split(HTMLRows6(ii).innerText, vbCrLf)(3))
                       country = Split(str2, ".")(0)
                       League = str2
                       Cells(j, "A").value = DateValue(mDate) + TimeValue(hourdate)
                       Cells(j, "I").value = "o"
                       lremove = Len(country) + 2
                       League = mId(League, lremove)
                       League = mId(League, 2)
                       League = Application.VLookup(League, sheet.Range("AN" & 2 & ":AR" & lr3), 5, False)
                       On Error Resume Next
                       country = Application.VLookup(country, sheet.Range("G" & 2 & ":J" & lr2), 2, False)
                       League = Application.VLookup(League, sheet.Range("AN" & 2 & ":AR" & lr3), 6, False)
                       Cells(j, "C").value = country
                       Cells(j, "D").value = League
                    ElseIf s = 1 Then
                       yy = 15
                    ElseIf s = 2 Then
                       yy = 1
                       On Error Resume Next
                       str = Application.VLookup(str, sheet.Range("AC" & 2 & ":AH" & lrdata), 6, False)
                    ElseIf s = 3 Then
                       yy = 14
                    ElseIf s = 4 Then
                       yy = 0
                       str = Replace(str, "X", "-")
                    ElseIf s = 5 Then
                       yy = 13
                    ElseIf s = 6 Then
                       yy = -1
                    End If
                    Cells(j, y + yy).value = str
                    y = y + 1
                    s = s + 1
                    If s = 7 Then
                       s = 0
                       yy = 0
                    End If
                    If y = 9 Then
                       j = j + 1
                       y = 2
                       If InStr(HTMLRows(i).innerText, "eller") <> 0 Then Exit For
                       If InStr(HTMLRows(i).innerText, "---") <> 0 Then Exit For
                       If InStr(HTMLRows(i).innerText, "Moneyline") <> 0 Then Exit For
                    End If
                 End If
              End If
           Next x
           ii = ii + 1
           GoTo step4
step5:

ita little on the slow end but it works