Coverting 1 letter codes into a sum value?
 

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

Coverting 1 letter codes into a sum value?
 

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"
wrote in
message
...

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

Coverting 1 letter codes into a sum value?
 

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

Coverting 1 letter codes into a sum value?
 

Thanks again for the help. The coding works perfectly.


--
Pookie76
------------------------------------------------------------------------
Pookie76's Profile: http://www.excelforum.com/member.php...o&userid=12815
View this thread: http://www.excelforum.com/showthread...hreadid=527647