Extract a word from text

[Marklarbear]

HI brains trust,

I'm after a formula to extract ONLY the word before the word "TEAMS" in a string of data.....

ie in cell A1 I have the following text: ROM MOMENTS NSW KOG TEAM 1

I want to extract the 4th word "KOG" into cell B1......

I have a rather large list of text strings in column A - the first 3 words vary in length, however the 4th word (ie KOG) is either 2 or 3 characters followed by exactly 7 characters (ie space TEAM space 1)

I've started with: =LEFT(A1,SEARCH("Team",A1&"TEAM")-2) however this gives me everything before the word TEAM - I only want the word immediately before TEAM not the rest of the text..... help????

 

[rlv01]

Perhaps something like this.

Book1
	A	B
1	ROM MOMENTS NSW KOG TEAM 1	KOG
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("TEAM",A1)-2)," ",REPT(" ",100)),50))

 

[Marklarbear]

Perhaps something like this.

Book1
	A	B
1	ROM MOMENTS NSW KOG TEAM 1	KOG
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("TEAM",A1)-2)," ",REPT(" ",100)),50))

That worked brilliantly!!! cheers

 

[Peter_SSs]

the 4th word (ie KOG) is either 2 or 3 characters followed by exactly 7 characters

In that case, this should be sufficient

22 11 24.xlsm
	A	B
1	ROM MOMENTS NSW KOG TEAM 1	KOG
2	Some other words TO Team 1	TO
Extract word

Cell Formulas
Range	Formula
B1:B2	B1	=TRIM(LEFT(RIGHT(A1,10),3))

 

[Peter_SSs]

Also, if it is the 4th word that is to be extracted and if you have the TEXTPLIT function you could use this

22 11 24.xlsm
	A	B
1	ROM MOMENTS NSW KOG TEAM 1	KOG
2	Some other words TO Team 1	TO
Extract word

Cell Formulas
Range	Formula
B1:B2	B1	=INDEX(TEXTSPLIT(A1," "),4)