Word count from word 1 to the first occurance of a particular phrase in a cell

[inosent]

Let's say I have this text in a cell, and I want to count all the words from word 1 to the first occurance of the phrase "Thank you":

"The quick brown fox jumped over the fence to say Hello.

Thank you

The blue car was parked on the street and ..."

The result is 11.

This works great for an overall word count, =IF(LEN(TRIM(M2))=0,0,LEN(TRIM(M2))-LEN(SUBSTITUTE(M2," ",""))+1)

but I only need to count the first part of the text in the cell as noted above

 

[jtakw]

Hi,

Try this:

Book1
	A	B
1	The quick brown fox jumped over the fence to say Hello. Thank you The blue car was parked on the street and	11
Sheet625

Cell Formulas
Range	Formula
B1	=LEN(TRIM(REPLACE(A1,SEARCH("Thank you",A1),LEN(A1),"")))-LEN(SUBSTITUTE(TRIM(REPLACE(A1,SEARCH("Thank you",A1),LEN(A1),""))," ",""))+1

 

[DanteAmor]

Try this too:

Excel Workbook
	L	M	N
1
2		The quick brown fox jumped over the fence to say Hello.Thank youThe blue car was parked on the street and	11
sheet1