sum numbers which contains characters

J

jacobs

in column A (A1:A11)
m
4A
8A
m
Tr
m
m
4A
4A
8A
8A

i would like to add up all the 4A and 8A so that my total column would
be 36A
how can i do that?
many thanks for any help
 
P

p45cal

You could try a user defined function used thus in a spreadsheet:
=SuffixSum(A1:A11,"A")
or
=SuffixSum(A1:A11,"B")

Function SuffixSum(TheRng, Suffix As String)
Dim SuffSum As Long
For Each cll In TheRng.Cells
mynumber = ""
For i = 1 To Len(cll)
OneChar = Mid(cll, i, 1)
If IsNumeric(OneChar) Then mynumber = mynumber & OneChar Else Exi
For
Next i
If OneChar = Suffix Then SuffSum = SuffSum + mynumber
Next cll
If SuffSum > 0 Then SuffixSum = SuffSum & Suffix
End Function

NOTE
Only integers are involved
It is case sensitive
Only one character can be used in the second argument
Only a number up to the first non-numeric character found in each cel
is considered
 
B

Bernard Liengme

If there will only be 4A and 8A (not 3A, 5A etc) then
=(COUNTIF(A1:A11,"4A")*4+COUNTIF(A1:A11,"8A")*8)&"A"
best wishes
 
Top