Sum numerical values in excel, ignoring text

Question

If I had cells containing, say,

• Red 4
• Blue 3
• 2
• 1

And I'm trying to sum the values, what formula could I use to derive the correct total, 10. The number will always be less than 10 and the rightmost character, if that helps.

Answer 1

If there is always only 1 digit and it is always on the end:

=SUM(IFERROR(--RIGHT(A1:A4,1),0))

Some Older versions will require the use of Ctrl-Shift-Enter instead of Enter when confirming the formula.

Right pulls the right most character and the -- tries to turn it from a string to a number, if it cannot become a number it will error and the IFERROR will capture that and make it 0. Then we sum the whole array.

Answer 2

You need a VBA function for summing up all numbers from a range of cells, even for the case that there are several numbers in the same cell.

The function can look like:

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
    Dim xNums As Variant, lngNum As Long
    For Each elem In rngS
      xNums = Split(elem, strDelim)
      For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
      Next lngNum
    Next elem
End Function

And may be called by a formula such as =SumNumbers(A5:B5):

To create the function, save the spreadsheet as an .xlsm file, type Alt+F11 to open the VBA editor, right-click "ThisWorkbook" and select Insert > Module, and copy-paste the above text inside the editor. Press Ctrl+S to save the spreadsheet, and then Alt+Q to close the VBA editor and return to Excel.