Formula to find all text before a given word

[Abhishek1988]

Hi Everyone,

I need help in excel. Please find below for example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Vikas Agarwal John Monty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Parul sharma Brown Peter Chris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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