Quote Originally Posted by jason.b75 View Post
Could you describe the basic purpose of the formula?

I was trying to step through it, but without some representative sample data it's hard to follow.
Hello,

Sure:

The goal is pretty simple:

I have data in cells that shows (each line is a cell):
[27/07/2012] <John> Waiting for news[15/06/2011] <john> In going
[20/06/2012] <John> no news[14/04/2011] <Mike> sent[27/07/2011]<Mike> waiting
[15/07/2012] <Mike> in going[27/07/2011] <Eddy> Draft in progress
[15/07/2012] <Mike> e-mail sent

I have many lines like this and what I want to do is keep only the last comment without the name or the date. So I must keep only the comment on the left (such as "waiting for news") without dates or names.
The IF() of the formula allows me to make the formula work when I have only one comment (as shown in the last lane: [15/07/2012] <Mike> e-mail sent).
Basiccaly, 2 formulas are created: one that take care of stripping down everything when I have 2 or more comments with dates and names; and the other one when there is only one comment with one name and one date.

Actually the correct formula is this one, there was one little typo in the one I put before:

=IF(LEN(D22)-LEN(SUBSTITUTE(D22;"[";""))>1;TRIM(MID(LEFT(D22;FIND("|";SUBSTITUTE(D22;"[";"|";2))-1);FIND("]";D22)+1;255));TRIM(MID(RIGHT(D22;FIND("|";SUBSTITUTE(D22;"<";"|";1))+255);FIND(">";D22)+1;255)))
So the =LEN(<range>)-LEN(SUBSTITUTE(<range>,"a","")) allowas me to count the number of occurence of the bracket"[". When it's above 1 it means I'm facing multiple comments. Then, the formula used is TRIM(MID(LEFT(D20;FIND("|";SUBSTITUTE(D20;"[";"|";2))-1);FIND("]";D20)+1;255))
which allows me to replace "[" by | and keep only what's on the left, the left comment.
Otherwise (if I have only 1 bracket. cf. IF statement), this formula is used (second part):TRIM(MID(RIGHT(D22;FIND("|";SUBSTITUTE(D22;"<";"|";1))+255);FIND(">";D22)+1;255)))
Here I use RIGHT instead of LEFT
the end result is this

Waiting for news
no news
in going
e-mail sent

I hope this helps! I think I forgot to explain a few points but I have to go now. I'll try to get back to you if some points are missing