asked on

Parsing a String

What is the best way to parse out a substring within the following string?

Daily Operations Status ReportDaily Operations Status ReportCritical Application Cycle End TimesInsurance/ISAInsurance DataBase(Critical Path - BT30004) ended at: 2:00Insurance DataBase(LASTISA) ended at: 3:00

I wrote the following parse function to pull out the time, but it returns: ) ended at: 2:00

Private Function FilterBT4(BT4Str)
Dim i As Integer, b As Integer
Dim FinalStr As String
i = InStr(1, BT4Str, "4) ended at: ")
If i Then
i = i + 1
b = InStr(i, BT4Str, "Insurance")
If b Then
FinalStr = Mid(BT4Str, i, b - i)
FilterBT4 = FinalStr
End If
End If
End Function

I just want it to pull out: 2:00

Any ideas??
Thanks
Rich

learning_t0_pr0gram

Dim pStart as Integer, pEnd as Integer
Dim pTime as String

pStart = InStr(1, Text1.Text, "ended at:") + 10
pEnd = InStr(pStart, Text1.Text, "Insurance")
pTime = Trim(Mid(Text1.Text, pStart, pEnd - pStart))

that is assuming the text u want to parse out of is Text1, u can change that

learning_t0_pr0gram

pTime would then be ur time.. u can test it by putting:
MsgBox pTime

Shauli

'Assuming BT4Str = "Daily Operations Status ReportDaily Operations Status ReportCritical Application Cycle End TimesInsurance/ISAInsurance DataBase(Critical Path - BT30004) ended at: 2:00Insurance DataBase(LASTISA) ended at: 3:00"

Then this is your function:

Private Function FilterBT4(BT4Str)
FilterBT4 = Right(BT4Str, Len(BT4Str) - InStrRev(BT4Str, "ended at:", -1) - 9)
End Function

S

Arthur_Wood

Shauli, your code will find the "ended at: 3:00" which is at the VERY END of the string, and not the ended at: 2:00 which the original questioner was searching for.

rkckjk, your original code:

Private Function FilterBT4(BT4Str)
Dim i As Integer, b As Integer
Dim FinalStr As String
i = InStr(1, BT4Str, "4) ended at: ")
If i Then
i = i + 1
b = InStr(i, BT4Str, "Insurance")
If b Then
FinalStr = Mid(BT4Str, i, b - i)
FilterBT4 = FinalStr
End If
End If
End Function

was VERY close, you simply needed to add 11 to i, rather than 1:

Private Function FilterBT4(BT4Str)
Dim i As Integer, b As Integer
Dim FinalStr As String
i = InStr(1, BT4Str, "4) ended at: ")
If i Then
i = i + 11
b = InStr(i, BT4Str, "Insurance")
If b Then
FinalStr = Mid(BT4Str, i, b - i)
FilterBT4 = FinalStr
End If
End If
End Function

The extra 10 is the number of characters in 'ended at: ', since the value of i (in the line -- i = InStr(1, BT4Str, "4) ended at: ")) is the location of the first 'e' in 'ended;, and you want the location of the 2 that foloows thast location...hence you must add the numbers of characters to offset to the right, to get the value you want.

AW

Shauli

Arthur_Wood, You are absolutely right, I misread the question :(
S

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial