• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting list items from single cell containing items and ranges

larryf

New Member
Hi all...

I frequently have to take the contents of a cell that has a mix of individual 5-digit codes and ranges of 5-digit codes--separated by commas--and create a list (or vector) of all constituent codes. As an example, I might be given a single cell containing: 10000-10002, 15555, 27984-27986. The number of elements can vary between input cells, but the end of any range is always the full five digits. In other words, the range 10000-10002 is always specified that way, and never as 10000-2. The outptut I'd like would be a list, like this:
10000
10001
10002
15555
27984
27985
27986

Is there some clever way to accomplish this? I spend an inordinate amount of time retyping these.

I appreciate any help you can provide!
 

Attachments

  • Extract ranges and items from list 20210423.xlsx
    9 KB · Views: 10
Try this formula solution with helper column

1] In "Helper 1" C7, formula copied down :
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A$3,",","</b><b>")&"</b></a>","//b["&ROW(A1)&"]"),"")

2] In "Helper 2" D7, formula copied across and down :
=IF((LEN($C7)=5)*(SUM($C7:C7)=$C7),$C7,IF(COLUMN(A1)<=-IMREAL(IMDIV($C7&"i","1+i"))*2+1,IMREAL($C7&"i")+COLUMN(A1)-1,""))

3] In "Result" A7, CSE (Ctrl+Shift+Enter) formula copied down :
=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF($D$7:$M$12<>"",$D$7:$M$12,""))&"</b></a>","//b["&ROW(A1)&"]"),"")

74235
 

Attachments

  • Extract ranges and items (BY).xlsx
    14.4 KB · Views: 6
An amazing tour-de-force; all achieved with legacy Excel!
The fluency with which data is extracted with xml, in combination with complex number processing shows complete mastery of the obscure!
 
It doesn't get that much easier using modern Excel,
74237
... and I have still to replace two of Charles Williams's FastExcel functions (SPLIT.TEXT and ACCUMULATE) with Lambda functions. Perhaps the only good thing to be said about the formula is that the output is a single dynamic array and no helper ranges are required.
 
Try this formula solution with helper column

1] In "Helper 1" C7, formula copied down :
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(A$3,",","</b><b>")&"</b></a>","//b["&ROW(A1)&"]"),"")

2] In "Helper 2" D7, formula copied across and down :
=IF((LEN($C7)=5)*(SUM($C7:C7)=$C7),$C7,IF(COLUMN(A1)<=-IMREAL(IMDIV($C7&"i","1+i"))*2+1,IMREAL($C7&"i")+COLUMN(A1)-1,""))

3] In "Result" A7, CSE (Ctrl+Shift+Enter) formula copied down :
=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF($D$7:$M$12<>"",$D$7:$M$12,""))&"</b></a>","//b["&ROW(A1)&"]"),"")

View attachment 74235
I am in awe and very grateful! This solution will materially improve my productivity (and eliminate mindless re-typing and the errors associated with it). Thanks so much for your help!
 
Note. This is of interest only if you have access to Excel 365 insider beta, which cuts it down a tad.
Rather than relying on Charles Williams's excellent FastExcel functions, I replaced them with a couple of λ-functions.
The first, SPLITXMLλ(list, comma), is lifted directly from @bosco_yip's solution
Code:
= LAMBDA(string,sep,
    LET(
      XML, "<list><item>"&
         SUBSTITUTE(string,sep,"</item><item>")&
         "</item></list>",
      XPath, "//item",
      FILTERXML(XML,XPath))
   )
(just a bit more verbose).
For the second, ACCUMULATEλ(terms), I chose to use matrix multiplication rather than recursion.
Code:
= LAMBDA(v,
    LET(
      n, COUNT(v),
      k, SEQUENCE(n),
      M, SIGN(TRANSPOSE(k)<k),
      1+MMULT(M, v))
   )
The formula itself now reads
Code:
= LET(
    comma, ",", hyphen, "-",
    ranges, SPLITXMLλ(list, comma),
    splits, IFERROR(SEARCH(hyphen, ranges), 0),
    start,  IF(splits, LEFT(ranges, splits-1 ), ranges),
    end,    IF(splits, MID(ranges, splits+1, 5), start),
    terms,  1 + (end - start),
    ptr,    ACCUMULATEλ(terms),
    k,      SEQUENCE(SUM(terms)),
    base,   XLOOKUP(k, ptr, start,,-1),
    offset, k - XLOOKUP(k, ptr, ptr,,-1),
  base+offset )
Perhaps there should be a prize for the smallest problem that demands the largest solution!
 

Attachments

  • Extract ranges and items (BY-PB).xlsx
    20.5 KB · Views: 7
Note. This is of interest only if you have access to Excel 365 insider beta, which cuts it down a tad.
Rather than relying on Charles Williams's excellent FastExcel functions, I replaced them with a couple of λ-functions.
The first, SPLITXMLλ(list, comma), is lifted directly from @bosco_yip's solution
Code:
= LAMBDA(string,sep,
    LET(
      XML, "<list><item>"&
         SUBSTITUTE(string,sep,"</item><item>")&
         "</item></list>",
      XPath, "//item",
      FILTERXML(XML,XPath))
   )
(just a bit more verbose).
For the second, ACCUMULATEλ(terms), I chose to use matrix multiplication rather than recursion.
Code:
= LAMBDA(v,
    LET(
      n, COUNT(v),
      k, SEQUENCE(n),
      M, SIGN(TRANSPOSE(k)<k),
      1+MMULT(M, v))
   )
The formula itself now reads
Code:
= LET(
    comma, ",", hyphen, "-",
    ranges, SPLITXMLλ(list, comma),
    splits, IFERROR(SEARCH(hyphen, ranges), 0),
    start,  IF(splits, LEFT(ranges, splits-1 ), ranges),
    end,    IF(splits, MID(ranges, splits+1, 5), start),
    terms,  1 + (end - start),
    ptr,    ACCUMULATEλ(terms),
    k,      SEQUENCE(SUM(terms)),
    base,   XLOOKUP(k, ptr, start,,-1),
    offset, k - XLOOKUP(k, ptr, ptr,,-1),
  base+offset )
Perhaps there should be a prize for the smallest problem that demands the largest solution!
I've been waiting for Microsoft to roll out Lambdas to the rest of us, but not with any actual project or use in mind. Now I've got a real reason to look forward to expanded availability. I honestly didn't know if this was a problem that could be solved with Excel, but you and bosco_yip each solved it in 24 hours. My hat's off to both of you!
 
Please try

Code:
=LET(a,SUBSTITUTE(FILTERXML("<x><m>"&SUBSTITUTE(A3,",","</m><m>")&"</m></x>","//m"),"-",REPT(" ",9)),
l,--LEFT(a,9),r,--RIGHT(a,9),s,l+SEQUENCE(,MAX(r-l)+1,0),b,IF(s<=r,s),MODE.MULT(b,b))
74247
 

Attachments

  • Extract ranges and items from list 20210423.xlsx
    10.3 KB · Views: 6
Last edited by a moderator:
Please try

Code:
=LET(a,SUBSTITUTE(FILTERXML("<x><m>"&SUBSTITUTE(A3,",","</m><m>")&"</m></x>","//m"),"-",REPT(" ",9)),
l,--LEFT(a,9),r,--RIGHT(a,9),s,l+SEQUENCE(,MAX(r-l)+1,0),b,IF(s<=r,s),MODE.MULT(b,b))
I tried it and it works great! Amazing solution. Thanks for your help!
 
Back
Top