Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Jacksonville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    String Frequency Counting in a Single Cell (Excel 2000)

    I want to know how frequent a text string occurs in another text string.

    i.e. CountFrequency (C1, D1) = 9

    where cell C1 contains "t" and cell D1 contains "I want to know how many times a text string occurs in another text string."

    Thanks for your help.

  2. #2
    New Lounger
    Join Date
    Sep 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String Frequency Counting in a Single Cell (Excel 2000)

    This is limited to a single character, but I can make it work for a string if you like. Create a function in VBA as follows: Open EXCEL, press Alt-F11 to open the VBA editor. Press Ctrl-R to ensure the Project Explorer is open. Single-Left-Click "VBAProject (the name of your workbook)". From the Top Menu, select Insert Module. A module screen will appear. Cut and Paste the following VBA code into the Module Screen:
    Function CountFrequency(vchar, vstr)
    n = 0
    For i = 1 To Len(vstr)
    If Mid(vstr, i, 1) = vchar Then n = n + 1
    Next i
    CountFrequency = n
    End Function

    Press Alt-F11 to go back to EXCEL. Then enter the following formula into any cell you want: =CountFrequency(C1, D1). I will count the number of times the character occurs. By the way, the answer is 10 times for the letter "t", not 9.

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: String Frequency Counting in a Single Cell (Excel 2000)

    Mark,

    This worksheet formula ought to work...
    =LEN(D1)-LEN(SUBSTITUTE(D1,C1,""))

    Regards,
    Jim Cone
    San Francisco, CA

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Jacksonville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String Frequency Counting in a Single Cell (Excel 2000)

    That's sexy. thanks.

    I added the "/LEN(C1)" to allow for any length of string.

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

Posting Permissions

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