Reading Text file and splitting dynamic sized string into array help

[BalloutMoe]

Hello all I have the following in a text file that is being read by vba and going line by line

60ANDREW COX 0.00A08:00 09:58 ANDREW COX
60DAVID BEAUMONT 0.00A08:08 13:26 14:09 18:00 D. BEAUMONT
60JOSEPG CAMACHO 0.00A08:04 11:10 11:41 18:01 JO CAMACHO
60JUAN CABRERA 0.00A08:00 18:02 JUAN CABRERA
60ROBERT ELMORE 0.00A08:04 18:02 ROBERT ELMOR
60RUBEN CABRERA 0.00A15:00 18:02 RUBEN CABRER
60TRISTON HILL 0.00A08:02 11:52 12:27 18:02 TRISTON HILL
60JOSE VARGAS 0.00A08:19 16:22 16:54 18:01 VARGAS
99 END OF DAY

I would like to split the strings for example

David Beamount	08:08	13:26
David Beamount	14:09	18:00
Josepg Camacho	08:04	11:10
Josepg Camacho	11:41	18:01

and So on for each employee. Sometime the row can have a bunch of different time clocks. Each two represent a clock in and clock out. How can I split it dynamically and enter it into a sheet? I have the following code below it works but only first the first clock and clock out. Thank you for your help

  For i = 0 To UBound(arrTxt)
  
      If InStr(arrTxt(i), todaysdate2) > 0 Then
         Do While InStr(arrTxt(i + j), "END OF DAY") = 0
            
            j = j + 1
            
            For t = 2 To 17
                                   If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
                                    'SI = InStr(arrTxt(i + j), "60ANDREW ")
                                    arrTime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")
                                    
                                    arrTime(0) = Right(arrTime(0), Len(arrTime(0)) - 2) & " " & arrTime(1)
                                    arrTime(2) = Split(arrTime(2), "A")(1)
                                    
                                            If arrTime(2) <= "08:10" Then
                                            arrTime(2) = "08:00"
                                            End If
                                     
                                            If arrTime(3) >= "17:53" And arrTime(3) <= "18:25" Then
                                            arrTime(3) = "18:00"
                                            End If
                          
                
                                            lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
                                            On Error Resume Next
                                            ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(1, 1).Value = arrTime(0)
                                            ThisWorkbook.Worksheets("Times").Range("B" & lastR2 + 1).Resize(1, 1).Value = arrTime(2)
                                            ThisWorkbook.Worksheets("Times").Range("C" & lastR2 + 1).Resize(1, 1).Value = arrTime(3)
                                            ThisWorkbook.Worksheets("Times").Range("D" & lastR2 + 1).Resize(1, 1).Value = todaysdate
                                            
                                  
                                   End If
                 Next
                                
         Loop 'For Do

      End If 'If Instr
  Next i

 

[BSALV]

the msgbox is only information, isn't necessary.

     If ptr > 0 Then Sheets("MySheet").ListObjects("MyListobject").ListRows.Add.Range.Range("A1").Resize(ptr, 4).Value = result     'write to output
     MsgBox IIf(ptr = 0, "nothing found", ptr & " lines added"), vbInformation     'msgbox

 

[BSALV]

change this line

             result(ptr, 1) = WorksheetFunction.Proper(Trim(Mid(sp(0), 3, Len(sp(0)) - 3)))   'Name

because in the corresponding lines in the textfile, the data is splitted on "0.00A" and for that person, there was still a "1" in front.
With that changed line above, you take a string that is 3 characters (2 at the LHS + 1 at the RHS) shorter + you trim the spaces

60CARLOS PONCE 0.00A08:16 17:58 CARLOS
60JASON GUERRERO 10.00A07:58 17:58 J. GUERRERO

 

[BalloutMoe]

change this line

             result(ptr, 1) = WorksheetFunction.Proper(Trim(Mid(sp(0), 3, Len(sp(0)) - 3)))   'Name

because in the corresponding lines in the textfile, the data is splitted on "0.00A" and for that person, there was still a "1" in front.
With that changed line above, you take a string that is 3 characters (2 at the LHS + 1 at the RHS) shorter + you trim the spaces

60CARLOS PONCE 0.00A08:16 17:58 CARLOS
60JASON GUERRERO 10.00A07:58 17:58 J. GUERRERO

Perfect, in this line I am entering it into a worksh
If ptr > 0 Then Sheets("MySheet").ListObjects("MyListobject").ListRows.Add.Range.Range("A1").Resize(ptr, 4).Value = result

change this line

             result(ptr, 1) = WorksheetFunction.Proper(Trim(Mid(sp(0), 3, Len(sp(0)) - 3)))   'Name

because in the corresponding lines in the textfile, the data is splitted on "0.00A" and for that person, there was still a "1" in front.
With that changed line above, you take a string that is 3 characters (2 at the LHS + 1 at the RHS) shorter + you trim the spaces

60CARLOS PONCE 0.00A08:16 17:58 CARLOS
60JASON GUERRERO 10.00A07:58 17:58 J. GUERRERO

Thank you very much it works perfectly.

 

[BSALV]

you're welcome