Count the number of WORDS before a certain word shows in string

[getstuffdone]

I was trying to figure out a way to count the number of words before a certain word shows in a string.
For example: I have to go shopping today.
Specific word to find: Shopping
Formula Result: 4

So there were 4 words before the word "shopping" came up. I was thinking of just cutting the cell wherever the specific word was and split it into two columns and then counting the total amount of words... but I was wondering if anyone had a quicker way.

 

[Eric W]

Welcome to the forum.

Maybe:

	A	B	C	D
1	I have to go shopping today.			shopping
2				4

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IFERROR(LEN(LEFT(A1,FIND(D1,A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(D1,A1))," ","")),0) <tbody> </tbody>

<tbody>
</tbody>

 

[gaz_chops]

Welcome to the forum.

Try

=LEN(REPLACE(A1,FIND(E1,A1),999,""))-LEN(SUBSTITUTE(REPLACE(A1,FIND(E1,A1),999,"")," ",""))

A1 is the text string
E1 is the word you want to find