Evaluate a variable string, return part of it to a new cell - vba or inherent fx? excel 2007

[exl_wiz]

Hi there.. I have a column which contains notes exported from a database. The notes are formatted with pipes as delimiters, there maybe 5-10 notes (delimited strings) in each cell of Col N. Each string will also contact a timestamp and the person's name who wrote the note, and the pipes appear at the start of each unique comment in the note string (before the emp name and timestamp).

I need to loop through each cell in this col, eval the whole string looking for a comment that contains canned text like "|*M: Expense approved - *. |"

the actual comment in the cell would have been like "|Bob Smith 1/1/2011 1:11 PM: Expense approved - Payee Name $0.00. |next note, etc."

so I want to look between the pipes for a note containing expense approval, and extract just that sentence including emp name and timestamp, payee and amt to a new cell in Col H.

VBA is ok, would like an in-cell function if there's a way to do this. I have countifs (as boolean) just to flag the presence of the constant part of the string "expense approved", but I have no idea how to pull out the sentence between the pipes where the constant matches..

suggestions?

 

[LloydFinancials]

Hi exl_wiz,

Can you give a particular example of a whole comment with all the notes and then what the solution would look like for that cell?