The key to success with this is the recursive use of InStr.
The function is called FindPar and the syntax is =FindPar (CellRef, "par(",Instance) where instance is 1, 2, 3 ... in other words the occurrence of par or bpar in the input string.
Function FindPar(Instring As String, Lookfor As String, Instance As Long) As String
Dim Rtnstring As String
Dim Position As Long
Dim StartPos As Long, EndPos As Long
Dim Counter As Long
Rtnstring = ""
Counter = 1
Position = InStr(1, Instring, Lookfor)
While Position > 0
If Counter = Instance Then
StartPos = InStr(Position, Instring, "(") + 1
EndPos = InStr(StartPos, Instring, ")")
FindPar = Mid(Instring, StartPos, EndPos - StartPos)
Exit Function
End If
Position = InStr(Position + 1, Instring, Lookfor)
Counter = Counter + 1
Wend
FindPar = Rtnstring
End Function
Bookmarks