find a group of characters in a range

[Jack Sons]

Hello all,

I know how to determine wether a group of characters, or the content of one
cell, is present somewhere among the content of another cell. For instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

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

But now I want to look into a range of cells, say A1 to A25. These cells can
have complex contents, I just want to now how many times the content of C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

 

[Gary''s Student]

Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.

 

[Ragdyer]

Or, just change Sum to Sumproduct to allow regular entry.

 

[Jack Sons]

GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

 

[Ragdyer]

Try this:

=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(C$1),"")))/LEN(C$1
))

 

[Rick Rothstein \(MVP - VB\)]

Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the one
Ragdyer posted both will count the word in C1 as being in the text even if
it is embedded within another word. For example, if you were searching for
the word "cat" and one of your cells had "I can concatenate the text" in it,
that would register as having the word "cat" in it because the word "cat" is
in the middle of the word "concatenate".

Rick

 

[Rick Rothstein \(MVP - VB\)]

A quick follow up question to Jack Sons. When you said, "I just want to now
how many times the content of C1 is present somewhere in the content of
cells in the range", how did you want to count it if the word in C1 appeared
more than once in a single cell? Does it add 1 or 2 to the total count you
are looking for. I ask because my formula counts it as 1 where as Ragdyer's
formula counts it as 2. I **think** my formula is what you are asking for,
but your introduction containing the Len-Len formula (which count multiple
occurrences as multiple hits) kind of confuses the overall question.

Rick

 

[Ragdyer]

Hey Rick,
Your suggested formula will *not* count multiple occurrences in the same
cell of the sought after string in C1.

If "catcat" is in A1, your formula returns 1,
where the others return 2.

 

[Ragdyer]

Sorry Rick,
Didn't see your post b4 I posted my last.

 

[Rick Rothstein \(MVP - VB\)]

Yes, you and I read the OP's question differently. His question really
didn't state clearly (at least to me) what he wanted to do. I guess we will
have to wait for him to come back to the thread and let us know what he
actually wanted.

Rick

 

[Ragdyer]

He did say that Gary's formula "worked like a charm"!<bg>

I only interjected to mention (belatedly) that Sumproduct() made arrays
unnecessary, and to also revise Gary's formula to *not* be case sensitive.

BTW ... to kick a dead horse <bg> ... 1 less function call:

=SUMPRODUCT(--ISNUMBER(SEARCH(C1,A1:A5)))

 

[Jack Sons]

Rick,

You are right. The number of occurences I am interested in is indeed the
number of cells that contain the reference group of characters. Even more
so, I would like to get "as output" also a list of the adresses of those
cells (my range is not necessarely one dimensional). Do you know how?

Jack.

 

[Jack Sons]

Thanks RD, very nice. See also the answer I just posted to Ricks earlier
post.

Jack.

 

[Rick Rothstein \(MVP - VB\)]

See inline...

He did say that Gary's formula "worked like a charm"!<bg>

But he may not have tested it with the text in C1 repeated within a single
cell in which case it may have only **looked** like it worked the way he
wanted. And this seems to be the case as I just got a response from the OP
to one of my other messages indicating my reading of the question was
correct.

I only interjected to mention (belatedly) that Sumproduct() made arrays
unnecessary, and to also revise Gary's formula to *not* be case sensitive.

BTW ... to kick a dead horse <bg> ... 1 less function call:

=SUMPRODUCT(--ISNUMBER(SEARCH(C1,A1:A5)))

Yes, I keep forgetting the --ISNUMBER in place of NOT(ISERROR) "trick"... I
know it, I just seem to have this mental block against using it for some
reason. Thanks for posting it.

Rick