how to count if cell "contains" a word

[cjlatta]

(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or ",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. I will try to show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. Can I do this?
Any help greatly appreciated.

 

[RagDyer]

Does this work for you:

=SUM(COUNTIF(A1:A5,{"*abcd, abcf*","*abcf,abcd*"}))

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

 

[robzrob]

(Using Excel2003/WinXP)  I am trying to count the number of times 2 values
are in a cell.  The string may contain the values ",abcd, abcf," or ",abcf,
abcd,"  The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer).  I am wanting to know the number of times thecell
contains "abcd" and "abcf" no matter what order it's in.  I will try toshow
an example below:

  Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1or
4.  Can I do this?
Any help greatly appreciated.

If you don't mind having another column and assuming the 'words' are
in B1, B2, etc, in C1 put =FIND("abcd",B1)*FIND("abcf",B1) and copy
down. Then at the bottom of column C, assuming 10 rows, put
=COUNTIF(C1:C10,">0").

 

[Ron Rosenfeld]

(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or ",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. I will try to show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. Can I do this?
Any help greatly appreciated.

If all the values are four characters, and/or there is no chance the string
being searched for could be found within another string (e.g. if 3, tabcdx, yz,
abcf is not a possibility) then:

=SUMPRODUCT(--ISNUMBER(SEARCH({"*abcd*abcf","*abcf*abcd"},A1:A5)))
--ron

 

[Rick Rothstein \(MVP - VB\)]

Why not this...

=SUM(COUNTIF(A1:A5,{"*abcd*abcf*","*abcf*abcd*"}))

and then it won't matter if "abcd, abcg, abcf" is in there?

Rick

 

[Ron Rosenfeld]

Does this work for you:

=SUM(COUNTIF(A1:A5,{"*abcd, abcf*","*abcf,abcd*"}))

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

Pasting in the OP's data, your formula returns a count of 2. The OP wanted a
count of 3.
--ron

 

[Ragdyer]

Why not?
I'll tell you why not.
I didn't think about it!<bg>

 

[Ragdyer]

Yeah!

I see my typo.

Left out a space between the 2 words in the 2nd half of the array constant.

Thanks.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

 

[cjlatta]

Unfortunately, not all the values are four characters. Like the suggestion
though!
Thanks for the info.

 

[cjlatta]

Thank you! This will do it for me, especially since the values can be in any
part of the sequence!

 

[cjlatta]

This looks really good to for another part of this problem. However, I can't
figure out how to do the "double find." When I enter the formula as written,
I get a Value! error. Do I need an extra set of parenthesis? Using your
example, I'm assuming that in C1, the result would be 1 (using the values I
have listed in my example), in C2 the result would be 2, in C3 the result
would be 2, in C4 the result would be 1 and in C5 the result would be 2?

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

The error is on purpose. FIND will return a value only if what it is
searching for exists, otherwise it returns an error. The COUNTIF function,
as set up, will only count values greater than zero... and error condition
is not a value greater than zero and hence won't be counted. This means that
the COUNTIF statement will only count cases where both FIND statements find
something and, since the order of multiplicands in multiplication doesn't
matter, the count will occur no matter what the order of the two strings
being searched for are in within the text.

Rick

 

[cjlatta]

Thanks for the clarification. I'm using many of these tips for this
particular spreadsheet - thanks for everyone's help.

 

[Ron Rosenfeld]

Unfortunately, not all the values are four characters. Like the suggestion
though!
Thanks for the info.

What I meant to write is that the technique of using wild cards (or FIND or
SEARCH) will not differentiate: abcd from abcde (or from any string in which
abcd is a substring.

If this is an issue, you can use a UDF such as:

============================
Option Explicit
Function StringCount(rg As Range, str1 As String, str2 As String) As Double
Dim c As Range
Dim re As Object, mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\b" & str1 & "\b.*\b" & str2 & _
"\b)|(\b" & str2 & "\b.*\b" & str1 & "\b)"

For Each c In rg
If re.test(c.Value) = True Then _
StringCount = StringCount + 1
Next c
End Function
=================================

To enter this UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code above into the window that opens.

To use this, enter a formula of the type:

=StringCount(A1:A10,"abcd","abcf")

The UDF requires that the two substrings be present (in any order) and it will
not count, for example, abcde when looking for abcd.
--ron

 

[Harlan Grove]

Why not this...

=SUM(COUNTIF(A1:A5,{"*abcd*abcf*","*abcf*abcd*"}))

....

Maybe in this particular case, but if these cells could contain values
like "abcde,abcFOOBAR" your formula would include them in the count
when they shouldn't be.

 

[Rick Rothstein \(MVP - VB\)]

That's true; but, of course, this is a problem for most search type
functions (FIND, SEARCH, InStr in VB, etc.). Of course, permitting these
items anywhere in the text seemed to be indicated given this example that
the OP included...

3 ,abcd, abcf, abcg,

where his inclusion of "abcg" (ignoring the commas) would seem to be your
FOOBAR case. I guess an argument could be made that at least the first one
might have to be located at the start of the text; but that were the
requirement, it could be covered by omitting the leading asterisk. in both
array strings.

Rick

 

[Harlan Grove]

That's true; but, of course, this is a problem for most search type
functions (FIND, SEARCH, InStr in VB, etc.). Of course, permitting
these items anywhere in the text seemed to be indicated given this
example that the OP included...

3 ,abcd, abcf, abcg,

....

Note the commas.

*YOUR* formula fails to make use of the commas.

Try the array formula

=COUNT(FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ",""))
+FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ","")))

 

[Rick Rothstein \(MVP - VB\)]

I guess I am missing your point. The way I read the initial post, the OP was
interested in finding the 4-character sequences without regard to the commas
(I assumed they were list delimiters), so it seemed to me that not factoring
them into the search was the thing to do. Are you suggesting the OP may have
been looking for something else that I am simply not seeing?

Rick

 

[Harlan Grove]

I guess I am missing your point. . . .
Clearly.

. . . The way I read the initial post, the OP was interested in
finding the 4-character sequences without regard to the commas
(I assumed they were list delimiters), . . .

Yes, it's PRECISELY because they'd be delimiters that it's A BIG
MISTAKE to discard them.

. . . so it seemed to me that not factoring them into the search
was the thing to do. . . .

Duh. Delimiters are usually CRITICAL. They're what allow for
distinguishing abcf a separate token sought from abcFUBAR a token not
necessarily being sought.

Regardless, your formula

=SUM(COUNTIF(A1:A5,{"*abcd*abcf*","*abcf*abcd*"}))

1. does too much work - if order is unimportant, then it's sufficient
to search for abcd and abcf, either as delimited tokens or simple
substrings, it's unnecessary to search for one then the other and the
other then the first;
2. is a bug in waiting - try your formula on the singe cell

0 ,abcx,abcd,abcf,abcd,xyz

In a single cell, should this be counted as 1 or 2?

Compare this to the results of my formula,

=COUNT(FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ",""))
+FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ","")))

If you don't like it as an array formula, make it

=SUMPRODUCT(--ISNUMBER(
FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ",""))
+FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ",""))
))

 

[Rick Rothstein \(MVP - VB\)]

. . . The way I read the initial post, the OP was interested in

Yes, it's PRECISELY because they'd be delimiters that it's A BIG
MISTAKE to discard them.


Duh. Delimiters are usually CRITICAL. They're what allow for
distinguishing abcf a separate token sought from abcFUBAR a token not
necessarily being sought.

Okay, I think this is where we are looking at the problem differently. I
read into the OP's examples that his delimited text were **all** 4
characters long. For that interpretation, the commas cannot affect the
search as there would be no way to get a false positive under that
condition.

Regardless, your formula

=SUM(COUNTIF(A1:A5,{"*abcd*abcf*","*abcf*abcd*"}))

1. does too much work - if order is unimportant, then it's sufficient
to search for abcd and abcf, either as delimited tokens or simple
substrings, it's unnecessary to search for one then the other and the
other then the first;
2. is a bug in waiting - try your formula on the singe cell

0 ,abcx,abcd,abcf,abcd,xyz

In a single cell, should this be counted as 1 or 2?

I don't know... in re-reading the original posting I am not totally sure;
however, I still lean to the OP wanting to count lines of occurrences as
opposed to total occurrence. And, I would point out that the OP did seem
satisfied with the results of my formula (actually, technically it isn't
"mine" as I only raised a question regarding making a minor change to the
formula RagDyer posted), so that may be an answer in itself.

Compare this to the results of my formula,

=COUNT(FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ",""))
+FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ","")))

If you don't like it as an array formula, make it

I have no problem with array formulas. You raise a good point, although
using my interpretation of the OP's setup, I would consider this
modification of your formula to answer his request...

=COUNT(FIND("abcd",A1:A5)+FIND("abcf",A1:A5))

Rick