Formula to Count Occurences of a Word in a Sentence Within a Cell

Hi All,

I am after the formula to count the occurrence of, for instance the word 'the' in a sentence/paragraph that is contained in Cell A1. Cell B1 should return the quantity of times the word 'the' has been found in Cell A1.
Thanks in Advance,

Cheers,
Mark

one way

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

Although this will count the within words, such aste there

EDIT: first section is repetition of Andy's post above

A basic

=(LEN(A1)-LEN(SUBSTITUTE(A1,"the","")))/LEN("the")

is risky given the word could be embedded... that said if it's a paragraph there's likely to be variations on punctuation, correct ?
(ie words split by space / comma / ? / full stop etc etc...)

If so a SUMPRODUCT (based on space delimiter) would not be watertight either... we'd need to know the punctuation variants I think.

Hi,

Please Login or Register  to view this content.

HTH

Just make the formula all upper case.

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"THE","")))/3