Hi Rick,

Can't thank you enough for this, this is really appreciated. Works
perfectly now with the Worksheet_change function.

Thanks again for your help and your persistence in sorting this for
me.

Kind Regards, Al.

On 27 Jun, 17:29, "Rick Rothstein \(MVP - VB\)"
wrote:
The first argument inside the brackets is a reference to the cell you want
to parse and the second argument is the phrase you want to find in order to
get the next two words after it. As for how to use the subroutine... well,
that depends on how your project is set up. Do you have a user form, perhaps
an ActiveX control on the spreadsheet, or maybe you want it to happen on its
own. Here is an example (not tested thoroughly, but it seem to work) where
the parsing takes place whenever anything is changed in column A (the column
where I am assuming your 1000 lines of long text strings are located).

On the sheet where the data is, right-click sheet tab and select View Code
from the popup menu that appears. You will be taken to the code window for
that worksheet. Paste the following into that code window...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo FixEvents
If ActiveCell.Column = 1 Then
Application.EnableEvents = False
GetAssignments Target, "The Assigned Individual has been changed to"
Application.EnableEvents = True
End If
Exit Sub
FixEvents:
Application.EnableEvents = True
End Sub

Sub GetAssignments(Cell As Range, Phrase As String)
Dim X As Long
Dim Contents As String
Dim Fields() As String
Dim Words() As String
If Not IsNull(Contents) Then Contents = Replace(Cell.Text, vbLf, " ")
Do While InStr(Contents, " ")
Contents = Replace(Contents, " ", " ")
Loop
Fields = Split(Contents, Phrase)
For X = 1 To UBound(Fields)
Words = Split(Trim$(Fields(X)), " ")
Cell.Offset(0, X).Value = Words(0) & " " & Words(1)
Next
End Sub

Now, place the long sample text you posted earlier into cell A1. Because the
newsreader turned the "soft" returns (vbLf) from the cell's text into "hard"
returns (vbCrLf), straight pasting will put the various lines into different
rows. For this example, what I did was to first delete the contents of cells
A2 through A14, then click on cell A1 and select all of its text on the
formula bar and then, finally, press Ctrl+V to paste the text into the cell
directly. As soon as you do that, you should see the two names pop up in
cells B1 and C1. I am presuming that however you now load the data into each
cell of the column, doing so will automatically kick off the subroutine and
parse the names out.

Rick

wrote in message

ups.com...



Hi Rick,

Thanks loads for your help with this. Slightly confused though as my
VBA knowledge isn't the best.

Do I have the parts in brackets after the sub name? If so, when I try
and run this as a macro Excel doesn't find this?

Also where does this statement "GetAssignments Range("A1"), "The
Assigned Individual has been changed to" need to go?

Sorry for this but thanks loads for your help so far.

KindRegards,Al.

On 27 Jun, 10:31, "Rick Rothstein \(MVP - VB\)"
wrote:
I think you can use this subroutine on a per row basis...

Sub GetAssignments(Cell As Range, Phrase As String)
Dim X As Long
Dim Fields() As String
Dim Words() As String
Fields = Split(Replace(Range("A1").Text, vbLf, " "), Phrase)
For X = 1 To UBound(Fields)
Words = Split(Trim$(Fields(X)), " ")
Cell.Offset(0, X).Value = Words(0) & " " & Words(1)
Next
End Sub

For your given example, this call....

GetAssignments Range("A1"), "The Assigned Individual has been changed to"

will place the name Gil Rogers in B1 and David Walker in C1. In your
code,
simply set up a loop through all the cells in your data column that have
data in them and, on each loop, pass in the cell address and your
find-phrase.

Rick

wrote in message

roups.com...

Hi Mike,

Apologies, my attention to detail was not on the ball tonight. The
proper text is "The Assigned Individual has been changed to".

Thanks and apologies, should have checked this before posting.

Al.

On 26 Jun, 20:58, Mike H wrote:
Me again

there are) I want to pull out the next two words after "This has
been
assigned to". This text may appear more than once, therefore
the
second entry should be put in column C etc. etc...

Baffled as to why my formula didn't work until i discovered (Should
have
checked first) the string you wanted to find doesn't exist in the text
:)

Please clarify

Mike

" wrote:
Hi Mike,

Please see below. Hope this helps and many thanks for your help
with
this.

Cheers,Al.

A1:
08/03/2007 14:03:32 m215039 Email Sent to Melanie.H CC:
BCC: Subject: Direct, Case ID:0000029967 Message: Hi Mel, just
to
confirm, we did have a problem cancelling in the system (see my note
below to Technical team). PO7500079598 has now been cancelled for
you by Dave Williams. Kindregards Gill 08.03.07- tried to
cancel order PO7500079598 on behalf of Mel (see email request from
Mel) The Case has been Closed. Resolution Code: Updated By:
Gill Walker 08/03/2007 13:55:30 087063 Case Assigned to Gill
Walker Email Text: This case has been assigned to you, please view
the
details below: The Assigned Individual has been changed to Gill
Walker Updated By: David W 08/03/2007 13:47:42 m215039 Case
Assigned to David W Email Text: This case has been assigned to you,
please view the details below: The Assigned Individual has been
changed to David Williams

On 26 Jun, 20:25, Mike H wrote:
That sounds very possible but for something like this it would be
really
helpful to see a string of data.

Mike

" wrote:
Is it possible to find a string of data within a cell and
extract
the
next two words when it appears?

I have a large amount of text within column A for approximately
1000
lines. In column B onwards (dependent upon how many unique
entries

I could do this for one entry using =FIND function however I'm
not
sure how to do this for multiple entries and also pull out the
next
two words as it's people's names so won't be a specific set of
characters.

Thanks in advance for your help with this.

Regards,Al.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -