In excel, can you create a named range from multiple values (linestring) within a single cell?

This could be quite a tricky problem. A line like '2,3,4' not so much, but '1-3, 5,7` is allready much more problematic, let alone a combination of the two.

It is allready made easier if you'd have access to LET() and microsoft 365's dynamic arrays:

Formula in C2:

=LET(A,TRANSPOSE(MID(FILTERXML("<t><s>'"&SUBSTITUTE(E1,",","</s><s>'")&"</s></t>","//s"),2,LEN(E1))),B,--LEFT(A,FIND("-",A&"-")-1),C,IFERROR(--MID(A,FIND("-",A)+1,99),--A),D,MMULT((A2:A6>=B)*(A2:A6<=C),SEQUENCE(COUNTA(A),,,0)),IF(D,SUM(B2:B6*D),0))

Variables explained:

• A - TRANSPOSE(MID(FILTERXML("<t><s>'"&SUBSTITUTE(E1,",","</s><s>'")&"</s></t>","//s"),2,LEN(E1))) will break up your initial string in an horizontal array. In our case {2,3,4,6-8}.
• B - --LEFT(A,FIND("-",A&"-")-1) will retrieve the number on the left of the hyphen. Note that we concatenate variable 'A' with an hyphen for this purpose. The result: {2,3,4,6}.
• C - IFERROR(--MID(A,FIND("-",A)+1,99),--A) will retrieve the number right of the hyphen. When no hyphen, the same as variable 'A' is returned: {2,3,4,8}.
• D - MMULT((A2:A6>=B)*(A2:A6<=C),SEQUENCE(COUNTA(A),,,0)) will calculate if any of your 'Nr.'s are to be found inbetween the previous two variables 'B' and 'C'. An vertical array is returned: {0;1;1;1;0}.
• IF(D,SUM(B2:B6*D),0) - Our final calculation is a simple IF() statement which will use D and spill the results down under 'Sum'.

I can get into much more detail, but the above should give you an idea of what happened here.

For those who find it interesting how to 'split' a string into an array, please follow this link.