String mittels links, rechts, teil aufsplitten

[Kuwer]

Hallo Connor,

per Makro z.B. so:

Sub TextzeitenZuSekunden()
 Dim lngZ As Long
 Dim varQ As Variant
 varQ = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
 With Application.WorksheetFunction
   For lngZ = 1 To Ubound(varQ)
     varQ(lngZ, 1) = Evaluate(.Substitute(.Substitute(.Substitute(.Substitute(varQ(lngZ, 1), " seconds", "*1"), " Minutes", "*60+"), " Hours", "*3600+"), " Days", "*86400+"))
   Next lngZ
 End With
 Range("B2").Resize(Ubound(varQ)).Value = varQ
End Sub

Die Ausgabe erfolgt in Spalte B in Sekunden.

Gruß Uwe

[GMG-CC]

Moin Connor,

als UDF (Funktion) bietet sich dieses an:

Code:

Option Explicit

Function Text2Time(c As Range) As Date
  Dim D As Integer, H As Integer, M As Integer, S As Integer
  Dim PosD As Integer, PosH As Integer, PosM As Integer, PosS As Integer
  Dim cSplit
 
  cSplit = Split(c, " ")
  Select Case UBound(cSplit)
  Case 7
     D = cSplit(0)
     H = cSplit(2)
     M = cSplit(4)
     S = cSplit(6)
  Case 5
     H = cSplit(0)
     M = cSplit(2)
     S = cSplit(4)
  Case 3
     M = cSplit(0)
     S = cSplit(2)
  Case Else
     S = cSplit(0)
  End Select
 
  Text2Time = TimeSerial(D * 24 + H, M, S)
End Function

Die Ausgabe erfolgt als Zeit, das Zahlenformat sollte [hh]:mm:ss sein.

[Connor]

Danke an alle für die tollen Lösungen "made my day!"