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

[JBeaucaire]

Received from an email:

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.

Please Login or Register  to view this content.

==========
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.

[pike]

hi JB

excelent

we need a "Free code Section"

[DonkeyOte]

Moved to Tips & Tutorials.

Worth adding perhaps that the same could be achieved natively with:

=(LEN(B1)-LEN(SUBSTITUTE(A1,B1,""))/LEN(B1)

where A1 is original string and B1 string of interest

And for range

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,""))))/LEN(B1)

(the latter could be applied via a single Evaluate call in UDF terms based on address of range specified in the args)

[JBeaucaire]

I had to move the parens to get Don's to work, but very slick indeed! Better than a UDF! Which is why I posted, wondered what the gang would come up with...

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

[DonkeyOte]

thanks JB - yes I missed the closing parenthesis (never code on the fly!)

[JBeaucaire]

I tried to get Don's formula to work inside the UDF and no dice. Anybody spot the error?

Please Login or Register  to view this content.

[DonkeyOte]

You need to encase your string variable within quotations and also when using Evaluate you should use .Address

One other point, when using Evaluate widely I'd say it's generally best to qualify the object against which you wish for it to be applied.

Please Login or Register  to view this content.

[teylyn]

why do you have that closing parens at the end? does MyStr open it?

MyStr & ")")

[DonkeyOte]

It is closing the LEN function

[bigdaddy187]

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)

I copy all the code. I'm trying to use the countstring formula but it doesn't work for me. . I get this "#NAME?" I have "WLLWWLLLLLLWWLLLWWWLLLWWLLWWLWLWLWLLLWWW" in cell A1. Any ideas why?

[teylyn]

Bigdaddy, please post questions in one of the question forums. This is not one. You can link to this thread, to provide the context.

Please make sure that when you quote, you use quote tags like this [quote] the quoted text[/quote], so people can actually identify where your contribution starts.

cheers

[bigdaddy187]

I seem to have gotten it to work... I open a new worksheet and the fuction worked. Thank you very much.

[JBeaucaire]

Teylyn, Bigdaddy is an alias for the guy who sent the original email.

[pike]

just a thought

Please Login or Register  to view this content.