Option Explicit
Sub Test()
Dim rng As Excel.Range
With Worksheets("Tabelle1")
Set rng = .Range("E2", .Cells(.Rows.Count, "E").End(xlUp))
If rng.Row < 2 Then Exit Sub
End With
Call rng.TextToColumns( _
Destination:=rng, _
FieldInfo:=Array(Array(1, xlYMDFormat), Array(2, xlGeneralFormat)), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True)
With rng.Offset(, 2)
.NumberFormat = "[$-F400]h:mm:ss AM/PM"
.FormulaR1C1 = "=--TEXT(RC[-1],""00\:00\:00"")"
.Value = .Value
End With
Call rng.Offset(, 1).Delete(xlShiftToLeft)
End Sub