Sum cells that have both alpha and numbers

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.

--
Biff
Microsoft Excel MVP


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

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

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"Bluthjen" wrote:

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

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



On Tue, 14 Apr 2009 15:19:01 -0700, Bluthjen
wrote:

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