Count all the words in a string before a specific word occur

Hi,

Is it possible to count the number of words in a string before a specific word?
Eg. John has left the building - count all before "building"=4
Andrew was there the night before - count all before "there"=2

I know how to count all the characters before another one and all the words in a string. Is it possible to count with criteria in string?

Regards,
Paul

With the sentence in A1 and specific word in B1

Please try C1

=LEN(LEFT(A1,FIND(B1,A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(B1,A1))," ",""))

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Many thanks to both of you. I've ended up using Bo_Ry's formula, but both of them will do. Thanks again