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
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...

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.

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
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...

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.

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. Kind regards 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
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...

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 -

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. Kind regards 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 -

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. Kind regards 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 -

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
ups.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. Kind regards 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 -