extracting text between two keywords
 

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!

extracting text between two keywords
 

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!

extracting text between two keywords
 

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!

extracting text between two keywords
 

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!

extracting text between two keywords
 

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!

extracting text between two keywords
 

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 -

extracting text between two keywords
 

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