Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting string occurrences (XP)

    How do I count the number of occurences of a string in a column the has multiple strings. Some are separated by a "-" and some are separated by a space. I can't change or modify the worksheet that contains the data.

  2. #2
    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: Counting string occurrences (XP)

    If I understand correctly. This formula, entered as an ARRAY (ctrl-shift-enter):
    <pre>=SUM((LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,C1,"")))/LEN(C1))</pre>


    Will count the total occurrences of the string you enter into the cell C1 in all the cells from A1:A100.

    If this is not what you are after, could you be a little more specific.

    Steve

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting string occurrences (XP)

    I tried that but couldn't get it to work.

    Here is an some data that im using trying to get this to work on:

    runapplication-unix-General Request-FTP
    runapplication-information technology-Functionality-FRED - NETWORK
    server-authentication-General Request-INFORMATION SECURITY - GENERIC
    runapplication-security-General Request-IS SECURITY
    runapplication-is support center-Functionality-VPN CLIENT CAMPUS/FIELD
    runapplication-is support center-Security-VPN CLIENT CAMPUS/FIELD
    runapplication-unix-Connection-FTP
    runapplication-is support center-General Request-VPN CLIENT CAMPUS/FIELD
    runapplication-security-Security-INTERNET FIREWALL
    runapplication-virus-General Request-MCAFEE
    runapplication-information technology-Feeds/Extracts-NETWORK MONITOR
    runapplication-security-Connection-FETCH.FRED.COM
    runapplication-datacomms-Infected-SNORT IDS CALL

    That data is in a column, each line in a separate row. From that I need to count the number of times a string appears, such as Snort or VPN, etc... With those counts i will make a graph to represent that result. Thanks

  4. #4
    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: Counting string occurrences (XP)

    Do you want a count of each "row" or the total of all the rows for each item?

    Look at the attached, maybe that will show what I meant. I calc'd for all the rows directly and also did a sum of the individ rows

    Steve

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting string occurrences (XP)

    Steve, Thanks. That will work. I didn't have the brackets, guess that's why it wasn't working. I have to pull a daily run to determine the number of times a type of call was placed. This will get me going, the ultimate would be to have a means to automate the means of querying a worksheet for various strings and tallying the results. Appreciate the help.

  6. #6
    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: Counting string occurrences (XP)

    The "brackets" come when you confirm with ctrl-shift-enter. It is an array formula.

    I also added the UPPER to ensure that it would be case insensitive. If Case is important you can remove the UPPER in the spreadsheet i had attached.

    Dteve

Posting Permissions

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