This UDF, which can be called from the spreadsheet, returns the number immediatly preceeding either "hr" or "urr".
Code:
Function numberWithin(inputString As String) As Double
Dim rightHalt As Integer, choppedStr As String, i As Long, flag As Boolean
rightHalt = InStr(inputString, "hr")
If rightHalt = 0 Then rightHalt = InStr(inputString, "uur")
choppedStr = Left(inputString, rightHalt)
For i = 1 To Len(choppedStr)
If flag Then
If Val(Right(choppedStr, i)) = 0 Then Exit Function
Else
If Val(Right(choppedStr, i)) < 0 Then flag = True
End If
numberWithin = Val(Right(choppedStr, i))
Next i
End Function
It returns the desired values of the examples given.
Quote:
example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)
Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs
Example j) 1) Nutricia Zoetermeer (migration to 8.2)
Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)
result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok
Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1
|