Formula to find all text before a given word

[Abhishek1988]

Hi Everyone,

I need help in excel. Please find below for example:

	A	B	C	D
1	Vikas Agarwal John Monty
2	Parul sharma Brown Peter Chris
3
4

<tbody>
</tbody>

i want to take all words mentioned before John/Peter. so my B1 should be Vikas Agarwal and B2 should be Parul sharma Brown.

please help

 

[arthurbr]

Are John and Peter ( or any other search string) always the second last word in the string?

 

[Abhishek1988]

No it could be anything.. I just made this but not able to put under multiple conditions

=TRIM(LEFT(A1, SEARCH(G5,A1)-1))

G5 - John

But i want to apply this to multiple condition. if G6 is Peter how can u put the same in above?

 

[arthurbr]

My question is : is the string to be searched ALWAYS the second last in the entire string?

 

[Sanjeev1976]

You can modify this formula to meet your requirement :

=TRIM(LEFT(A1,FIND(G1,A1,1)-2))

 

[Abhishek1988]

No it could be different.

Name could be Alex Harmer John Michael Hughes. I want to text before John or peter (as per my question) no matter wherever they are placed

 

[Scott Huish]

=IFERROR(TRIM(LEFT(A1,LOOKUP(9.99999999E+307,SEARCH({"John","Peter"},A1))-1)),"")

Excel 2010
	A	B
1	Vikas Agarwal John Monty	Vikas Agarwal
2	Parul sharma Brown Peter Chris	Parul sharma Brown
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(LEFT(A1,LOOKUP(9.99999999E+307,SEARCH({"John","Peter"},A1))-1)),"")
B2	=IFERROR(TRIM(LEFT(A2,LOOKUP(9.99999999E+307,SEARCH({"John","Peter"},A2))-1)),"")