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

Sum cells that have both alpha and numbers
 

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

Sum cells that have both alpha and numbers
 

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

Sum cells that have both alpha and numbers
 

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