extracting text between two keywords

[Michelle]

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!

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[Michelle]

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.

 

[T. Valko]

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.

 

[Michelle]

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

 

[Pete_UK]

Sorry about that - try this instead:

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

Hope this helps.

Pete

 

[Glenn]

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",REPT(" ",999)),"NAME:",
REPT(" ",999)),999,999))