MS Excel: Count Number of Words Between Two Different Characters in a Text String

Question

I am looking for a MS Excel (2007 or 2010) formula that will return the number of words found between two (2) specific characters within a text string.

Example (In Cell A2): John Doe purchased a pizza for $13.00 using his 10% discount coupon on his order.

I would like to Count the number words separated by spaces between the "$" (dollar symbol) and the "%" (percent symbol); including the words containing the specific characters.

Results (In Cell B2): The formula in cell A2 returns a value of four (4) to reflect that the string contains four words ($13.00 using his 10%) separated by "$" and "%".

Answer 1

Place this in B2, changing "%" and "$" as required:

=LEN(MID(B2,FIND("$",B2)+1,FIND("%",B2)-FIND("$",B2)-1))-LEN(SUBSTITUTE(MID(B2,FIND("$",B2)+1,FIND("%",B2)-FIND("$",B2)-1)," ",""))+1