Sum cells that have both alpha and numbers

[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

 

[T. Valko]

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.

 

[Sheeloo]

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

 

[Gord Dibben]

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