I have a block of wrapped text in cell D2. Buried within the text are two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I need.

I appreciate any help from the masses..
Thanks!

Try this:

=MID(D2,SEARCH("NAME:",D2)+6,LEN(D2)-SEARCH(" constrained",D2)+7-SEARCH
("name:",D2))

Hope this helps.

Pete


On Feb 11, 4:37*pm, Michelle
wrote:
I have a block of wrapped text in cell D2. Buried within the text are two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I need..

I appreciate any help from the masses..
Thanks!

Almost... it picks up after "NAME:" as intended, but then continues right on
past "CONSTRAINED" and gives me the entire block of text to the end.

"Pete_UK" wrote:

Try this:

=MID(D2,SEARCH("NAME:",D2)+6,LEN(D2)-SEARCH(" constrained",D2)+7-SEARCH
("name:",D2))

Hope this helps.

Pete


On Feb 11, 4:37 pm, Michelle
wrote:
I have a block of wrapped text in cell D2. Buried within the text are two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I need..

I appreciate any help from the masses..
Thanks!

Try this:

=MID(D2,SEARCH("name:",D2)+6,SEARCH("constrained", D2)-(SEARCH("name:",D2)+7))

This assumes there is a space character after Name: and before Constrained.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
Almost... it picks up after "NAME:" as intended, but then continues right
on
past "CONSTRAINED" and gives me the entire block of text to the end.

"Pete_UK" wrote:

Try this:

=MID(D2,SEARCH("NAME:",D2)+6,LEN(D2)-SEARCH(" constrained",D2)+7-SEARCH
("name:",D2))

Hope this helps.

Pete


On Feb 11, 4:37 pm, Michelle
wrote:
I have a block of wrapped text in cell D2. Buried within the text are
two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I
need..

I appreciate any help from the masses..
Thanks!

Thank you!

Answer
Put the string in A1
Put the first keyword in A2
Put the second keyword in A3
In A4 enter:

=MID(A1,FIND(A2,A1)+LEN(A2),FIND(A3,A1)-FIND(A2,A1)-LEN(A2))

for example:

wkhf78fhjaNAME:123456789CONSTRAINED987vos
NAME:
CONSTRAINED
123456789

--
Gary''s Student - gsnu200832

"Michelle" wrote:

Almost... it picks up after "NAME:" as intended, but then continues right on
past "CONSTRAINED" and gives me the entire block of text to the end.

"Pete_UK" wrote:

Try this:

=MID(D2,SEARCH("NAME:",D2)+6,LEN(D2)-SEARCH(" constrained",D2)+7-SEARCH
("name:",D2))

Hope this helps.

Pete


On Feb 11, 4:37 pm, Michelle
wrote:
I have a block of wrapped text in cell D2. Buried within the text are two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I need..

I appreciate any help from the masses..
Thanks!

Sorry about that - try this instead:

=MID(D2,SEARCH("NAME:",D2)+6,SEARCH(" constrained",D2)-6-SEARCH
("name:",D2))

Hope this helps.

Pete


On Feb 11, 5:07*pm, Michelle
wrote:
Almost... it picks up after "NAME:" as intended, but then continues right on
past "CONSTRAINED" and gives me the entire block of text to the end.



"Pete_UK" wrote:
Try this:

=MID(D2,SEARCH("NAME:",D2)+6,LEN(D2)-SEARCH(" constrained",D2)+7-SEARCH
("name:",D2))

Hope this helps.

Pete

On Feb 11, 4:37 pm, Michelle
wrote:
I have a block of wrapped text in cell D2. Buried within the text are two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I need..

I appreciate any help from the masses..
Thanks!- Hide quoted text -

- Show quoted text -

Michelle wrote:
I have a block of wrapped text in cell D2. Buried within the text are two
keywords: "NAME:" and "CONSTRAINED". I want to extract all of the text
between these two keywords and paste it into cell L2.

I feel I'm close with

=MID(D2,SEARCH("NAME:",D2),30)

but since I haven't told it where to stop, this isn't exactly what I need.

I appreciate any help from the masses..
Thanks!


Another way:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(D2,"CONSTRAINED",R EPT(" ",999)),"NAME:",
REPT(" ",999)),999,999))