SUM of column with some text fields

Question

Looking to see if I can get the SUM of an enter column, including columns that have a symbol and then the number. However the ones with symbols are actually text fields.

2
1
- → 2
3

Any ideas? I've tried a few different things including using RIGHT, but still sees it as text and doesn't add it.

Answer 1

Try the following User Defined Function:

Public Function Zum(rng As Range) As Variant
    Dim r As Range, s As String, L As Long, i As Long
    Dim numbr As String, CH As String

    Zum = 0
    For Each r In rng
        s = r.Text
        If s <> "" Then
            L = Len(s)
            numbr = ""
            For i = 1 To L
                CH = Mid(s, i, 1)
                If CH Like "[0-9]" Or CH = "." Then
                    numbr = numbr & CH
                End If
            Next i
            If numbr <> "" Then
                Zum = Zum + CDbl(numbr)
            End If
        End If
    Next r
End Function

For example:

It will add cells, but remove anything except digits and the decimal point from the cells before doing the addition.

EDIT#1:

This version of the code only handles the right-most digits of each cell,

ignoring alphabets and the decimal point:

Public Function Zum(rng As Range) As Variant

'   version #2  only sum the right-most digits and ignore the decimal point


    Dim r As Range, s As String, L As Long, i As Long
    Dim numbr As String, CH As String

    Zum = 0
    For Each r In rng
        s = r.Text
        If s <> "" Then
            L = Len(s)
            numbr = ""
            For i = L To 1 Step -1
                CH = Mid(s, i, 1)
                If CH Like "[0-9]" Then
                    numbr = CH & numbr
                Else
                    Exit For
                End If
            Next i
            If numbr <> "" Then
                Zum = Zum + CDbl(numbr)
            End If
        End If
    Next r
End Function

and if you want only the single right-most digit:

Public Function Zum(rng As Range) As Variant

'   version #3  only sum the right-most digit and ignore the decimal point


    Dim r As Range, s As String, L As Long, i As Long
    Dim numbr As String, CH As String

    Zum = 0
    For Each r In rng
        s = r.Text
        If s <> "" Then
            CH = Right(s, 1)
            If CH Like "[0-9]" Then
                Zum = Zum + CLng(CH)
            End If
        End If
    Next r
End Function

Answer 2

This one requires you to slide this all the way down as a helper column but you can just sum that column after. It assumes that there will be at most one character at the beginning followed by the spaces and arrows before the desired number. Let me know if this isn't what you expected.

=IF(MID(A11,3,1)="→", --RIGHT(A11,LEN(A11)-4),A11)

This might work more reliably so long as it is at most 2 digits for the second number =IF(ISERR(SEARCH("→",A1)),A1,--RIGHT(A1,2))

EDIT: This should take care of the non-values, but Scott's answer seems much closer to what you wanted. I'm not sure how you would edit his to make it take into account the non-value cells though.

=IF(ISNUMBER(A4),A4,IF(MID(A4,3,1)="→", IFERROR(--RIGHT(A4,LEN(A4)-4),"")),"")