Coverting string of text into individual sums?

P

Pookie76

Is it possible for me to have excel sum all the one letter codes into
individual cells and let me know the count?

Ie. (AAABBCDDDD) A=3, B=2, C=1, D=4
and have those sums placed in an individual cell for each?

Thanks for the help.
 
R

Ron Coderre

Try this:

For a value in A2
And B1: A
And C1: B

B2: =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),B$1,""))
C2: =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),C$1,""))

Those formulas return the counts of "A" and "B" in cell A2
respectively.

Is that something you can work with?

Ro
 
D

Don Guillett

try
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",""))
or if b1 contains A
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),b1,""))
 
D

Domenic

Assuming that A2 contains the text string, let C2:C5 contain A, B, C,
and D, then enter the following formula in D2 and copy down:

=SUMPRODUCT(--(MID($A$2,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A$2))),1)=C2)
)

Hope this helps!
 
Top