Counting specific words in a sibgle cell

[DiamondGeezer]

Greetings all you Masters of Excel

What I need to do is count the number of occurrences there are of a given word in a single cell (Excel2007)

example
cell contents = pig pig cow sheep pig horse
how many 'pig'
answer 3
be fab to have a percentage as well e.g 50% of words

Whose got the magic to meet this need?
Thank you

 

[VoG]

Try

=(LEN(A1)-LEN(SUBSTITUTE(A1,"pig","")))/3

 

[Peter_SSs]

Welcome to the MrExcel board!

If the cell might contain, for example, "pig pig cow sheep pig horse pigeon"
then Peter's solution will return 4. If you are looking for just whole words, you might need something like this (though it looks like there should be a simpler way). Formula copied down.

Excel Workbook
	A	B	C	D
1	pig pig cow sheep pig horse pigeon	3		pig
2	cow sheep pigeon pig horse	1
3	pig dog	1
4	dog	0
5	pig pig pig	3
6	pig	1
7	pigeon	0
8	ape pig pig bat	2
Count words