Coverting 1 letter codes into a sum value?

[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.

[Chip Pearson]

Try some code like the following:

Dim S As String
Dim Total As Double
Dim Ndx As Long
S = "AAABBCCDD"
For Ndx = 1 To Len(S)
Select Case UCase(Mid(S, Ndx, 1))
Case "A"
Total = Total + 1
Case "B"
Total = Total + 2
Case "C"
Total = Total + 3
Case "D"
Total = Total + 4
Case Else
End Select
Next Ndx
Debug.Print Total



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Pookie76"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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.
>
>
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile:
> http://www.excelforum.com/member.php...o&userid=12815
> View this thread:
> http://www.excelforum.com/showthread...hreadid=527647
>

[Carim]

Hi Pookie,

With formulas, assuming your input cell is A1 :
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
=LEN(A1)-LEN(SUBSTITUTE(A1,"B",""))
=LEN(A1)-LEN(SUBSTITUTE(A1,"C",""))
=LEN(A1)-LEN(SUBSTITUTE(A1,"D",""))
HTH
Carim

[Pookie76]

Thanks again for the help. The coding works perfectly.