Extracting 2 or 3 words before a match found

[satish78]

Hi Friends,

I tried to extract first 2 words using this =TRIM(RIGHT(A1, FIND(B1",SUBSTITUTE(A1, " ", B1,2)&B1)))
below is the text in ColumnA

A1=Refresh staging database use data pump
B1=Data
C1= =TRIM(RIGHT(A1, FIND(B1",SUBSTITUTE(A1, " ", B1,2)&B1)))
some how did not work

Can anyone let me know where I been wrong?

 

[Scott Huish]

Try this:
=TRIM(LEFT(A1,SEARCH(B1,A1)-1))

 

[Rick Rothstein]

Hi Friends,

I tried to extract first 2 words using this =TRIM(RIGHT(A1, FIND(B1",SUBSTITUTE(A1, " ", B1,2)&B1)))
below is the text in ColumnA

A1=Refresh staging database use data pump
B1=Data
C1= =TRIM(RIGHT(A1, FIND(B1",SUBSTITUTE(A1, " ", B1,2)&B1)))
some how did not work

Can anyone let me know where I been wrong?

Did you want to match "data" in "database" or the free-standing "data" toward the end of the text?

 

[satish78]

Rick.

only "data" I need to look.

 

[satish78]

Scott
I need to extract only 2 words before the word "Data".
You formula extracts whatever the content present before the word "data"

Try this:
=TRIM(LEFT(A1,SEARCH(B1,A1)-1))

 

[jtakw]

Hi,

So is the correct result in your sample "Refresh staging" or "database use"?

 

[Rick Rothstein]

Rick.

only "data" I need to look.

Assuming the word "data" has only space characters next to it (unless it is at the end of the text in which case it has only a space character in front of it), give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,SEARCH(" "&B1&" "," "&A1&" ")-1))," ",REPT(" ",300)),600),600))

 

[jtakw]

Hi,

So is the correct result in your sample "Refresh staging" or "database use"?

If it's the latter, you can also try this:

Book1
	A	B	G
1	Refresh staging database use data pump	Data	database use
Sheet29

Cell Formulas
Range	Formula
G1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH(" "&B1&" ",A1)-1)," ",REPT(" ",250)),500))

 

[satish78]

what if I have range of words from B1:10

 

[Aladin Akyurek]

what if I have range of words from B1:10

Try to post what you have in B1:B10 and also indicate the expected result(s).