Sum formula that ignores characters in a cell that aren't numbers

MikeWells13 

Copy the following custom VBA function into a standard module in the Visual Basic Editor:

Function SumNumbers(rng As Range) As Double
    Dim a As Variant
    Dim v As Variant
    Dim s As String
    Dim i As Long
    a = rng.Value
    For Each v In a
        s = ""
        For i = 1 To Len(v)
            If IsNumeric(Mid(v, i, 1)) Then
                s = s & Mid(v, i, 1)
            End If
        Next i
        SumNumbers = SumNumbers + Val(s)
    Next v
End Function

Switch back to Excel

Use the function as follows in a cell formula:

=SumNumbers(A1:A4)

where A1:A4 is a range with mixed text characters and digits.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.