Delete part of a text in cells.

**Jeffy14** · 08-15-2012, 09:49 AM

Hello everyone!

I have a small problem and I'm wondering if it can be done with VBA.
I have cells with text (comments) in them. I need to clean up the text and keep only the last comment in the cell. For example, one cell is displayed like this:
[26/07/2012] <JohnDoe> Rate confirmed
[25/07/2012] <JohnDoe> Waiting for rate

Those 2 lines are only once cell. It can also be written this way: [26/07/2012] <JohnDoe> Rate confirmed[25/07/2012] <JohnDoe> Waiting for rate

I need to keep the last comment on the left or top (with or without the date within brackets and with or with the angle brackets, it does not matter) and delete everything that's on the right from the first bracket.
So I need this:
[26/07/2012] <JohnDoe> Rate confirmed
or I need this:
Rate confirmed

Here, only the last comment is kept and everything on the right was deleted. I need to do this many times in multiples cells, that's why I need a maco.
Do you think it's possible?

Can I write a code saying to delete everything on the right starting from the second pair of brackets?

I considered using <*> but the problem is that it keeps the comments on the right

I would really appreciate your help guys!

Thx a lot!

**Marcol** · 08-15-2012, 10:06 AM

Why do you need a macro?

Assuming yur data is in A2 Down try ....
In B2, Drag/Fill Down as required

Formula:

Please Login or Register  to view this content.

Or without the date

Formula:

Please Login or Register  to view this content.

**Jeffy14** · 08-15-2012, 11:38 AM

omg it works!
You are my hero. I'll try to see more carefully what you did because it's a little above my skill

Why do you use "|" please?
What if I also wanna take down the last <> with the name in your second formula please?
I could use REPLACE ALL: <*>
But can it be done with the formula please? Thx a lot!

**Jeffy14** · 08-15-2012, 11:48 AM

Oh sorry no there is a problem. I see what you did. But the problem is that the comment is not always the same length and the length of the name can change too.
The date and the name are always the same length but the comment in the middle could be different. So the number of character can change.

Exemple:

[26/07/2012] <JohnDoe> Rate confirmed[25/07/2012] <JohnDoe> Waiting for rate
[22/08/2012] <MikeAbbot> Affrimation in progress[14/05/2012] <JohnDoe> Still waiting
[01/04/2012] <EricCanto> confirmation sent[1/03/2012] <JimmyShoo> In progress

Each line represent a different cell.
You see my problem?
Thx a lot!

**Marcol** · 08-15-2012, 01:09 PM

The formulae work for me, regardless of the number of characters between "]" & "["
Did you use the fill handle to drag the formula down?

We are interested in the postion of the second "[" in the string, so using SUBSTITUTE(A2,"[","|",2)
We get From A2
[26/07/2012] <JohnDoe> Rate confirmed|25/07/2012] <JohnDoe> Waiting for rate
We then use FIND() to get the position of "|" in the result.

See this workbook for some more examples.

**Jeffy14** · 08-15-2012, 01:29 PM

you are right! I'm so sorry. It works.
Thanks a lot for your help!!!
This is so awesome.

**Cutter** · 08-15-2012, 02:00 PM

@ Jeffy14

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Jeffy14** · 08-15-2012, 02:36 PM

Originally Posted by Cutter

@ Jeffy14

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Hello,

SOrry about that. I'll read the rules now.
Thx for reminding me.