PDA

View Full Version : Find and Delete text from a cell...



rrtts
10-27-2006, 04:03 PM
I have a message (similar to an email header as shown below).

-----Original Message-----
From: First Name, Last Name
Sent: Wednesday, October 25, 2006 4:11
To: First Name, Last Name
First Name, Last Name
First Name, Last Name
Cc: First Name, Last Name
Subject: TEST
Body: Text
EOM: End of Message

I have a macro that strips the header and footer off the message and just leaves the people it was sent "TO:" as shown below.

(This information is displayed in column A, BUT the "TO:" isn't necessarily always in the same cell location.)

To: First Name, Last Name
First Name, Last Name
First Name, Last Name

The problem is I am trying to get rid of the "TO:" and just have names, as shown below.

First Name, Last Name
First Name, Last Name
First Name, Last Name

I have tried to use forms of TRIM, LEFT, FIND, and a few other processes...but I keep running into problems with the rest of the data being effected or the cell/row/sheet that has "TO:" being modified/deleted. For example, the name Tom gets clipped as well.

Any ideas...I think VB would work better since I could just add it to the current macro I use to strip the header and footer, and since data in this column is listed using a formula.

I'm sure it is something extremely simple that I am overlooking. Thanks.

~signed

still a noob...but learning...

webtubbs
10-27-2006, 06:08 PM
You might like to try SUBSTITUTE....

=SUBSTITUTE(A1, "To: ", "")

Or even do a Replace. Go to Edit > Replace and enter "To: " in "Find what:" and leave "Replace with:" empty.

lucas
10-28-2006, 07:05 AM
I used the recorder to get this:

Sub Macro1()
Cells.Replace What:="To:", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub