Results 1 to 14 of 14

Finding the frequency of a string in other string(s)

Threaded View

JBeaucaire Finding the frequency of a... 12-08-2009, 01:48 AM
pike Re: Finding the frequency of... 12-08-2009, 01:56 AM
DonkeyOte Re: Finding the frequency of... 12-08-2009, 03:43 AM
JBeaucaire Re: Finding the frequency of... 12-08-2009, 04:50 AM
DonkeyOte Re: Finding the frequency of... 12-08-2009, 04:57 AM
JBeaucaire Re: Finding the frequency of... 12-08-2009, 05:01 AM
DonkeyOte Re: Finding the frequency of... 12-08-2009, 05:11 AM
teylyn Re: Finding the frequency of... 12-08-2009, 05:14 AM
DonkeyOte Re: Finding the frequency of... 12-08-2009, 05:18 AM
bigdaddy187 Re: Finding the frequency of... 12-08-2009, 06:57 PM
teylyn Re: Finding the frequency of... 12-08-2009, 07:03 PM
bigdaddy187 Re: Finding the frequency of... 12-08-2009, 07:09 PM
JBeaucaire Re: Finding the frequency of... 12-08-2009, 09:09 PM
pike Re: Finding the frequency of... 01-05-2010, 05:57 AM
  1. #1
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Finding the frequency of a string in other string(s)

    Received from an email:
    Quote Originally Posted by vkcham van
    I'm looking for a formula that will count specific word in a cell.

    Let say cell a1 has "WLLWWLLLLLLWWLLLWWWLLLWWLLWWLWLWLWLLLWWW"
    I want to know how many WWLL are in this cell, which is 4.

    Thank you - sam
    I wrote this custom function to provide the answer. It is used like so:

    =COUNTSTRING(A1, "WWLL")
    =COUNTSTRING(A1:A10, "WWLL")
    =COUNTSTRING(A1, B1)
    (B1 holds the text string WWLL)

    The first parameter is a cell or range of cells. The second parameter is the string to search for as a single cell reference or text string.

    Function COUNTSTRING(RNG As Range, MyStr As String) As Long
    'JBeaucaire  (12/8/2009)
    'Count the frequency of a string in other strings
    Dim cell As Range, MyCnt As Long, i As Long
    If MyStr = "" Then GoTo ErrorExit
    
        For Each cell In RNG
            For i = 1 To (Len(cell) - Len(MyStr) + 1)
                If Mid(cell, i, Len(MyStr)) = MyStr Then MyCnt = MyCnt + 1
            Next i
        Next cell
    
    COUNTSTRING = MyCnt
    Exit Function
    
    ErrorExit:
        COUNTSTRING = 0
    End Function
    ==========
    How to install the User Defined Function:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The function is installed and ready to use in cell as shown above.
    Last edited by JBeaucaire; 12-08-2009 at 09:09 PM. Reason: Fixed the "For i =" line of code
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1