Counting Specific Character(s) In A Range?

[Guest]

Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character “W†it
gives you a reply of 3 (A1, A2 & A3) and when for “WH†it returns “2†(A1 &
A3).

Thanx in advance & Best Regards

 

[Rick Rothstein \(MVP - VB\)]

If your range is fixed like you show, and if the text you want to find is in
B1, then you can use this...

=IF(B1<>"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPPER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against that
character being in the "find" text.

Rick

 

[Guest]

YAHOOOOOOOOO!
Thanx Rick!!!
U R Great!!!!!

If your range is fixed like you show, and if the text you want to find is in
B1, then you can use this...

=IF(B1<>"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPPER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against that
character being in the "find" text.

Rick

Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character “Wâ€
it
gives you a reply of 3 (A1, A2 & A3) and when for “WH†it returns “2†(A1
&
A3).

Thanx in advance & Best Regards

 

[Guest]

By the way Rick! If there isn't any repetition of same character in one cell.
Any idea of using some sort of sumproduct() funtion like:

=Sumproduct(--(find("W",A1:A3)>0))?

Concatenations of a large range otherwise would take too much length and
formula would not be possible.

The strategy presented former however I do still accept was outstanding and
truly a GREAT one!

If your range is fixed like you show, and if the text you want to find is in
B1, then you can use this...

=IF(B1<>"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPPER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against that
character being in the "find" text.

Rick

Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character “Wâ€
it
gives you a reply of 3 (A1, A2 & A3) and when for “WH†it returns “2†(A1
&
A3).

Thanx in advance & Best Regards

 

[Guest]

Dear Rick,

I am just being nosy and this thread is of interest to me. I was wondering
if the data extended to say 100 rows, is it possible to count the specific
characters.

Thanks.

 

[Rick Rothstein \(MVP - VB\)]

If you have a large range, and if there could be repeats within a cell, you
could do this instead. Assuming your text phrases are in column A and B1
holds the text you want to search for, put this formula

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),LEN($B$1)),$B$1)))

in a blank column and copy it down. What the formula does is list the number
of times the text in B1 appears in each cell in column A; so, to get your
total, just SUM up the column you placed the above formulas in. If you don't
want to show these individual counts, hide the column and put your SUM
formula in a different column

Rick

By the way Rick! If there isn't any repetition of same character in one
cell.
Any idea of using some sort of sumproduct() funtion like:

=Sumproduct(--(find("W",A1:A3)>0))?

Concatenations of a large range otherwise would take too much length and
formula would not be possible.

The strategy presented former however I do still accept was outstanding
and
truly a GREAT one!

If your range is fixed like you show, and if the text you want to find is
in
B1, then you can use this...

=IF(B1<>"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPPER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will
have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't
end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against
that
character being in the "find" text.

Rick

Can someone devise a formula to sum-up the number of times a character
or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character
“Wâ€
it
gives you a reply of 3 (A1, A2 & A3) and when for “WH†it returns “2â€
(A1
&
A3).

Thanx in advance & Best Regards

 

[Rick Rothstein \(MVP - VB\)]

If you have a large range, and if there could be repeats within a cell,

you could do this instead. Assuming your text phrases are in column A and
B1 holds the text you want to search for, put this formula

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),LEN($B$1)),$B$1)))

in a blank column and copy it down. What the formula does is list the
number of times the text in B1 appears in each cell in column A; so, to
get your total, just SUM up the column you placed the above formulas in.
If you don't want to show these individual counts, hide the column and put
your SUM formula in a different column.

Never mind... don't use this formula, it fails under certain circumstances.

Rick

 

[Rick Rothstein \(MVP - VB\)]

If you have a large range, and if there could be repeats within a cell,

Never mind... don't use this formula, it fails under certain
circumstances.

This formula works...

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1&"|",ROW(INDIRECT("A$1:A"&LEN(A1))),LEN($B$1)),$B$1)))

Any character concatenated on the end of the text in the cell being searched
(I used the vertical bar), as long as that character will never appear in
your text, stops the miscount. So, use the above formula, copy it down and
SUM the result as I described in my second post.

Rick

 

[Guest]

Hey Guys!

CHIP PEARSON has justhelped me in an xclent development in this regard i.e.:

For single character
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("w"),"")))

& for string:
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("wh"),"")))/LEN("wh")

 

[Guest]

You can also use

=SUMPRODUCT(--(LEN(A1:A3)>LEN(SUBSTITUTE(UPPER(A1:A3),"W",""))))

 

[Guest]

Thanx Duke,

But your recommendation in my opinion won't workout for multiple "w" in a
single cell!