Count occurrences of text within a cell

[Stan Sitek]

I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0

 

[T. Valko]

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E","")))

 

[Peo Sjoblom]

If you only use one cell then this will suffice

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E",""))

you probably meant


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"E","")))

 

[T. Valko]

Ooops!

Yeah, I meant your first example:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E",""))

 

[Deena]

Can you please tell me where a good tutorial on this subject is? Or what is
the significance of each part of this formula? I'm trying to expand my
knowledge of Excel. The formula works to count the number of 'V's' (in my
spreadsheet) in a range but I would like to return a value of 4 for each 'V'
counted.

Thanks.

 

[Gord Dibben]

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"V","")))*4

For more on SUMPRODUCT see Bob Phillips' site.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Gord Dibben MS Excel MVP