Count number in cell which have both comma and dash in one cell-CodePudding

Example

In Cell "A2" I have 2,4,5,7-9

How do I count them by using formula or coding with vba

and count them as 2 4 5 7 8 9 which sum up value to 6

and return value in Cell "B2"

CodePudding user response：

With VBA,

Function AddNumbers(rngTarget As Range) As Long
    Dim arrValues() As String
    Dim lngValue As Long
    Dim strValue As String
    Dim lngMinimum As Long
    Dim lngMaximum As Long

    arrValues = Split(rngTarget.Text, ",")
    
    For lngValue = LBound(arrValues) To UBound(arrValues)
    
        strValue = arrValues(lngValue)
                
        If InStr(strValue, "-") > 0 Then
        
            lngMinimum = CLng(Left(strValue, InStr(strValue, "-") - 1))
            
            lngMaximum = CLng(Replace(strValue, lngMinimum & "-", vbNullString))
        
            AddNumbers = AddNumbers   ((lngMaximum - lngMinimum)   1)
        
        Else
        
            AddNumbers = AddNumbers   1
        
        End If
    
    Next lngValue
        
End Function

CodePudding user response：

Assuming column XFD in the active sheet is empty, and that no integer within the string will ever exceed 2^20:

=SUM(COUNTIF(INDIRECT("XFD"&SUBSTITUTE(TEXTSPLIT(A2,","),"-",":XFD")),""))

For those without TEXTSPLIT:

=SUM(COUNTIF(INDIRECT(SUBSTITUTE(FILTERXML("<a><b>XFD"&SUBSTITUTE(A2,",","</b><b>XFD")&"</b></a>","//b"),"-",":XFD")),""))