Sum cells that have both alpha and numbers

  • Thread starter Thread starter Bluthjen
  • Start date Start date
B

Bluthjen

I need to know how to add up the numbers in a cell that has both alpha and
numbers.

example

english 36
PE 44
Math 16
History 65

I need to add the number on the bottom but keep it in the same column
 
Assuming the number is *always* preceded by a space character and there is
*always* just a single space character.

Try this array formula** :

=SUM(IF(ISNUMBER(-RIGHT(A1:A4)),--MID(A1:A4,FIND(" ",A1:A4)+1,5)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
If all numbers are two digits then enter this in A5
=SUM(RIGHT(A1:A4,2)*1)
and press CTRL-SHIFT-ENTER

You can adjust the range to you numbers and enter in the first blank cell...
 
Do you mean Sum 36, 44, 16 and 65 to return 161?

Function AddItUp(Range_to_add As Range)
'Sandy Mann Feb 26, 2007
'SUM numbers in text strings
Dim Cell As Range
Dim X As Integer
Dim cVal As Double
Dim Tot As Double
For Each Cell In Range_to_add
For X = 1 To Len(Cell)
If IsNumeric(Mid(Cell.Value, X, 1)) Then
cVal = cVal * 10 + Mid(Cell.Value, X, 1)
End If
Next X
Tot = Tot + cVal
cVal = 0
Next Cell
AddItUp = Tot
End Function

Enter =AddItUp(A1:A4) in A5


Gord Dibben MS Excel MVP
 
Back
Top