Thread: count items in a sentence (2002)

1. count items in a sentence (2002)

I would like to see if we can count the number to times the word "on" appears in a sentence in cells A1:A10.

2. Re: count items in a sentence (2002)

This formula just counts the "on" 's :

=(LEN(A1)-LEN(SUBSTITUTE(A1,"on","")))/2

Up to you whether or not to count " on" or " on " or "on "

3. Re: count items in a sentence (2002)

If you are only looking for the word "on", then you will need " on ". " on" will pick up both "on" and "one". "on " will pick up both "on" and "decision". The reason for looking for the space is that is how you tell that it is just the word "on" and nothing else. HTH

4. Re: count items in a sentence (2002)

Dont' think this is what I am looking for. Doesn't LEN count the number characters in a word? Hope this is not asking to much.... Could you explain the formula? Trying to see if I can understand how you came up with it. Also, not sure why it makes a difference if its " on", " on " , "on ". Since I am only looking for a word in sentences in cells A1 thru A10 shouldn't it just look for the word and not worry it is got a space. In this example shouldn't the count I am looking for be 4? What if it finds the word "one" would it count the "on" in it?

5. Re: count items in a sentence (2002)

Yes, but one does want to count situations where a sentence begins with on or ends with it.......

6. Re: count items in a sentence (2002)

Good point. Don't we then need to test for "On " - as it is case sensitive - and for " on."?

7. Re: count items in a sentence (2002)

See if this User Defined Function will give you what you want:

<pre>Public Function CountWord(strWord As String, oRng As Range) As Long
Dim lPos As Long, lLast As Long
Dim strWk As String
Dim oCell As Range
For Each oCell In oRng
olast = 0
strWk = UCase(oCell.Value)
lPos = InStr(strWk, "ON")
Do While lPos > 0
If lPos = 1 And InStr(" ,.?!:;""'", Mid(strWk, 3, 1)) > 0 Then
CountWord = CountWord + 1
Else
If InStr(" ,.?!:;""'", Mid(strWk, lPos - 1, 1)) > 0 Then
If lPos + 1 = Len(strWk) Then
CountWord = CountWord + 1
Else
If InStr(" ,.?!:;""'", Mid(strWk, lPos + 2, 1)) > 0 Then
CountWord = CountWord + 1
End If
End If
End If
End If
lPos = InStr(lPos + 1, strWk, "ON")
Loop
Next oCell
End Function
</pre>

8. Re: count items in a sentence (2002)

Sorry but that is over my head. How and where would I add this?

9. Re: count items in a sentence (2002)

<img src=/S/grin.gif border=0 alt=grin width=15 height=15> JanKarel

<<< Up to you whether or not to count " on" or " on " or "on " >>>

You Kidder You <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Wassim

10. Re: count items in a sentence (2002)

Shouldn't:
lPos = InStr(strWk, "ON")
lPos = InStr(lPos + 1, strWk, "ON")
be:
lPos = InStr(strWk, ucase(strWord))
lPos = InStr(lPos + 1, strWk, ucase(strWord))

Steve

11. Re: count items in a sentence (2002)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> dreyers

OK waiting for Jan to explain the formula, I will try to pipe in and see if I do a decent job:

OK <<< =(LEN(A1)-LEN(SUBSTITUTE(A1,"on","")))/2 >>>

First lets go to the inner Parans. because they will be evaluated first, so you have SUBSTITUTE(A1,"on","") which removed all the "on" and make them empty "".
Second we do the next Parans. LEN(SUBSTITUTE(A1,"on","")) will get the length of the string in A1 after the substitution of the "" to the "on".
Third we have the subtraction of the real length from the length after the substitution, thus the difference will be how many characters have been substituted and then you divide by 2 because "on" has two characters.

Neat <img src=/S/grin.gif border=0 alt=grin width=15 height=15>!!! This is the best, and fastest way to do this job, vs looping through the string.

Wassim

12. Re: count items in a sentence (2002)

so, the total divided is the number of times that ON showed up in the spreadsheet? When I pasted the formula in C1 it only gave me the number (1) for A1. I don't think it check from A1 thru A10. Which should be around 4 times the word ON showed up.
??

This has got to be the dummest request from management.

13. Re: count items in a sentence (2002)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> dreyes

No this formula will not check a range, just a cell A1.

I don't know if this type of a formula can be used as an array formula, so the best thing to do, is to fill down and then sum the column.

BTW did you answer Jan Karel's question about counting " on" or " on " or "on "? What that questions is really asking is that do you want to look for the string "ON", or the word "On". You see the spaces, <font color=blue>(which made me laugh like crazy as soon as I saw the post... You know count on and on and on.... well counting goes on and on ...</font color=blue> and this is what Jan Karel is asking, " on" counts any word that starts with 'ON', " on " counts the word, and "on " counts words ending with 'on'

Hope this helps.

Wassim

14. Re: count items in a sentence (2002)

LOL... Maybe I should have used a word like... TREE instead of ON.

Jan: I want to count the " on " and I understand now about copying the formula down each line. Thank you

15. Re: count items in a sentence (2002)

The ARRAY formula (ctrl-shift-enter) should work:

=SUM((LEN(A1:A11)-LEN(SUBSTITUTE(A1:A11,"on","")))/2)
as a modification to Jan's.

It still does NOT prevent finding Once, action, etc which "contain" the word ON. Legare's function should take care of that.

Steve

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•