Excel count amount of numbers separated by a comma with range from x to y

user_16807185 0
Viewed 110

I have numbers separated by commas and also ranges of numbers (for example, 1-5 means 1,2,3,4,5). I would like to return the total quantity of numbers in the string, no matter how many ranges, if any, are in it.

I can't really come up with an idea, without making the data static (for example, splitting it into different columns).

The below function works on strings without any ranges

=LEN(TRIM(C10))-LEN(SUBSTITUTE(TRIM(C10);",";""))+1

but the main problem is that I have numbers like "1-5, 8, 10, 15" where it should return 8 as the amount of numbers in the cell.

https://i.stack.imgur.com/s4GWr.png

excel-formula excel
4 Answers

OK - with Excel 2019 and the craziest formula ever:

=SUM( IF( ISTEXT(( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) )), VALUE( RIGHT( ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ), LEN( ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ) ) - FIND( "-", ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) )) ) ) - VALUE(LEFT( ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ), FIND( "-", ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ) )-1 ) ) +  1, IF( YEAR( ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ) ) > 1900, ABS( MONTH(( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ))-DAY( ( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ) ) ) +1, 1 ) ) )

Which is an expansion of:

=LET( string, C10,
       a, TRANSPOSE( FILTERXML( "<main><sub>" & SUBSTITUTE( C10, ",", "</sub><sub>" ) & "</sub></main>", "//sub" ) ),
       SUM( IF( ISTEXT(a), VALUE( RIGHT( a, LEN( a ) - FIND( "-", a) ) ) - VALUE(LEFT( a, FIND( "-", a )-1 ) ) +  1,
                            IF( YEAR( a ) > 1900, ABS( MONTH(a)-DAY(a ) ) +1, 1 ) ) )  )
user_46171210

I suggest a User Defined Function written with VBA:

Function countNumbersInString(S As String) As Long

   Dim v, w, x, i As Long, col As Collection

v = Split(S, ",")

Set col = New Collection
    For Each w In v
        If w Like ("*-*") Then
             x = Split(w, "-")
             For i = x(0) To x(1)
                col.Add i
            Next i
        Else
            col.Add CLng(w)
        End If
    Next w

    countNumbersInString = col.Count
            
End Function
user_28729220

You could try the following:

enter image description here

Formula in B1:

=IFERROR(SUM(RIGHT(SUBSTITUTE(FILTERXML("<t><s>`"&SUBSTITUTE(A1;",";"</s><s>`")&"</s></t>";"//s[contains(.,'-')]");"-";REPT(" ";LEN(A1)));LEN(A1))-LEFT(SUBSTITUTE(FILTERXML("<t><s>+"&SUBSTITUTE(A1;",";"</s><s>+")&"</s></t>";"//s[contains(.,'-')]");"-";REPT(" ";LEN(A1)));LEN(A1))+1);0)+IFERROR(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1;",";"</s><s>")&"</s></t>";"//s[.*0=0]"));0)

Confirm as array-formula through CtrlShiftEnter

user_97581940

I would do something like:

=RIGHT(C10, LEN(C10)-SEARCH("-", C10))-
LEFT(C10, SEARCH("-", C10)-1)+1

enter image description here

user_109627890
Related