if i have a paragraph in one cell
and want to count the number of words in the paragraph, what do i do

say the cell has the following "I went to the school today. School was
fun." all in a1,

how do i count the number of times school is in a1?


--
kckar
------------------------------------------------------------------------
kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322
View this thread: http://www.excelforum.com/showthread...hreadid=512954

Try

=(LEN(A1)-LEN(SUBSTITUTE(A1,"school","")))/6

The 6 is the length of the word for which you're searching, if you put
your search word in a cell, say C2

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=512954

wouldnt that count all 2 letter words such as "is" and "it" instead of
just counting "it"

or all words that have seven letters instead of just counting how many
times school shows up


--
kckar
------------------------------------------------------------------------
kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322
View this thread: http://www.excelforum.com/showthread...hreadid=512954

No because it specifically looks for the text 'school', not just any old 6
character string. It just divides by 6 as the first part of the formula
counts the number of characters taken up by the word school (12 if there are
2), and so needs to divide by the length of the search.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"kckar" wrote in
message ...

wouldnt that count all 2 letter words such as "is" and "it" instead of
just counting "it"

or all words that have seven letters instead of just counting how many
times school shows up


--
kckar
------------------------------------------------------------------------
kckar's Profile:
http://www.excelforum.com/member.php...o&userid=25322
View this thread: http://www.excelforum.com/showthread...hreadid=512954

You might set up a user defined function like:

_ _ _ _ _ _ _ _ _ _

Function CountWdInstances(rngA As Range, strSeek As String) As Integer
arrayWords = Split(LCase(rngA.Text))
arrFilteredList = Filter(arrayWords, strSeek)
CountWdInstances = UBound(arrFilteredList) + 1
End Function
_ _ _ _ _ _ _ _ _ _


Now, if you use
=CountWdInstances(A1, "school")
You should get the number of times "school" appears in the range. If you
want it to be case sensitive, drop the LCase inside the Split function.

Steve Yandl


"kckar" wrote in
message ...

if i have a paragraph in one cell
and want to count the number of words in the paragraph, what do i do

say the cell has the following "I went to the school today. School was
fun." all in a1,

how do i count the number of times school is in a1?


--
kckar
------------------------------------------------------------------------
kckar's Profile:
http://www.excelforum.com/member.php...o&userid=25322
View this thread: http://www.excelforum.com/showthread...hreadid=512954