# 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.

#### Attachments

• 9 KB Views: 8

#### bosco_yip

##### Excel Ninja
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)&"]"),"") #### Attachments

• 14.4 KB Views: 6
• • larryf, Peter Bartholomew, deciog and 1 other person

#### Peter Bartholomew

##### Well-Known Member
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!

• larryf

#### Peter Bartholomew

##### Well-Known Member
• larryf

#### larryf

##### New Member
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!

#### Peter Bartholomew

##### Well-Known Member
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))
)``````
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

• 20.5 KB Views: 5
• deciog

#### larryf

##### New Member
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))
)``````
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!

• Peter Bartholomew

#### Attachments

• 10.3 KB Views: 5
Last edited by a moderator:
• • larryf, deciog, Peter Bartholomew and 1 other person

#### Peter Bartholomew

##### Well-Known Member
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))``````
@Excel Wizard
You seem to specialise in the concise! I padded it out, mainly with line-feeds, until I could see what was going on. The duplicate MODE.MULT trick is neat.

• Excel Wizard

#### larryf

##### New Member
``````=LET(a,SUBSTITUTE(FILTERXML("<x><m>"&SUBSTITUTE(A3,",","</m><m>")&"</m></x>","//m"),"-",REPT(" ",9)),