Brute force pbtrace.log processing (revisited)

An update to my previous post on using Excel to parse a pbtrace.log file.

The problem with the function in the second column is that it's trying to find the last occurance of a "(", and it's doing it by assuming that it can start at a certain fixed position before the end of the string. That turns out to be an issue.

Excel doesn't have a LastPos function, but VBA does. So you can create a VBA macro that does a LastPos and then call that from an Excel function. Launch the VBA editor in Excel, and then create a function as:

Public Function FindRev(sFind_Text As String, sWithin_Text As String) As Integer
FindRev = InStrRev(sWithin_Text, sFind_Text, -1, vbTextCompare)
End Function

Now in the second column, use this function instead:

=IF(A1>0,FindRev("(",D1),0)

The other thing you may want to consider is adding Conditional Formatting to Column C in order to make the problemattic statements more obvious. Select Column C, select Format-> Conditional Formatting from the menu, and then enter your criteria. In this example, I'm setting the background color of the cell to red if the statement took more that 1000 milliseconds (1 second):