a text formula that i cant crack!

[monsierexcel]

I am trying to make a formula which basically says "find the first word that starts with G then choose the word AFTER it" but i cant configure the formula correctly, i am a bit of a novice!
i can do it by combining IF statementslike this:

=IF(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("G",B4),LEN(B4))," ",REPT(" ",100)),100))="G",TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("G",B4),LEN(B4))," ",REPT(" ",100)),100))&" "&E4,TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("G",B4),LEN(B4))," ",REPT(" ",100)),100)))


but not in one formula.

example text: Bobs Golden Ticket
I want the word "ticket" to show in my formula.

thank you for your help!

 

[sandy666]

what if:

Greg Smith Ticket ==> Smith or Ticket ?
Gregs Golden Ticket ==> ???
Mark Train Green ==> ???

more representative example with expected result, please

 

[Phuoc]

Try:

=TRIM(MID(SUBSTITUTE(MID(B4,FIND("G",B4),LEN(B4))," ",REPT(" ",100)),100,100))

 

[monsierexcel]

Greg Smith Ticket ==> Smith
Gregs Golden Ticket ==> Golden
Mark Train Green ==> n/a or failure

 

[monsierexcel]

thank you this worked - i am hoping to adapt it so i can use 3rd or 4th words too.

 

[jtakw]

Hi,

Use B1 formula for in case your 1st G word may Not be Capitalized.
Use C1 formula, references E1 (user input) for nth word of your choice starting at 1st G word.

Book1
	A	B	C	D	E
1	Bobs Golden Ticket abc def ghi	Ticket	def	nth word starting at 1st G word	4
2	Greg Smith Ticket abc def ghi	Smith	abc		^^ user input
3	Gregs Golden Ticket abc def ghi	Golden	abc
4	Mark Train Green
5	A good samaritan abc def ghi	samaritan	def
6	bring Goodies abc def ghi	abc	ghi
7	bring girl scout cookies abc def ghi	scout	abc
Sheet426

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(MID(" "&A1,SEARCH(" G"," "&A1),255)," ",REPT(" ",100)),200,100))
C1	=TRIM(MID(SUBSTITUTE(MID(" "&A1,SEARCH(" G"," "&A1),255)," ",REPT(" ",100)),E$1*100,100))