Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 "
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Gre

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.......
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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."?
    Gre

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count items in a sentence (2002)

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

  9. #9
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    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>

    Made my day!!!

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #11
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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 LastLast

Posting Permissions

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