If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
how to count if cell "contains" a word
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! "robzrob" wrote: On Jul 11, 11:20 pm, cjlatta wrote: (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 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"). |
#12
|
|||
|
|||
how to count if cell "contains" a word
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" wrote in message news 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! "robzrob" wrote: On Jul 11, 11:20 pm, cjlatta wrote: (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 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"). |
#13
|
|||
|
|||
how to count if cell "contains" a word
Thanks for the clarification. I'm using many of these tips for this
particular spreadsheet - thanks for everyone's help. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message news 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! "robzrob" wrote: On Jul 11, 11:20 pm, cjlatta wrote: (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 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"). |
#14
|
|||
|
|||
how to count if cell "contains" a word
On Mon, 14 Jul 2008 07:15:00 -0700, cjlatta
wrote: 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 |
#15
|
|||
|
|||
how to count if cell "contains" a word
"Rick Rothstein \(MVP - VB\)" wrote...
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. |
#16
|
|||
|
|||
how to count if cell "contains" a word
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" wrote in message ... "Rick Rothstein \(MVP - VB\)" wrote... 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. |
#17
|
|||
|
|||
how to count if cell "contains" a word
"Rick Rothstein \(MVP - VB\)" wrote...
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&","," ",""))) |
#18
|
|||
|
|||
how to count if cell "contains" a word
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" wrote in message ... "Rick Rothstein \(MVP - VB\)" wrote... 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&","," ",""))) |
#19
|
|||
|
|||
how to count if cell "contains" a word
"Rick Rothstein \(MVP - VB\)" wrote...
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&","," ","")) )) |
#20
|
|||
|
|||
how to count if cell "contains" a word
. . . 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. 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 |
Thread Tools | |
Display Modes | |
|
|