3

This is related this question. The OP proposed to give inputs to a formula that contain a list of connection quantities and speeds like this: 1x1000,2x200,1x50 would mean that there is one 1000k connection, two 200k and 1 50k. I would like to parse this into an array table like this:
1 1000
2 200
1 50

I tried this formula, but it only produces the left hand side of the table:

=LET( case, A5,
       a, FILTERXML("<t><s>"&SUBSTITUTE(case,",","</s><s>")&"</s></t>","//s[contains(., 'x')]"),
       FILTERXML("<t><s>"&SUBSTITUTE(a,"x","</s><s>")&"</s></t>","//s") )

where case is the input variable, a parses the table into strings containing "x" (this is to ensure that only valid "q x speed" strings are used. I then tried to split this array and... no joy.

From this post by JvdV, I think the answer can be found in the xpath, but I cannot find a solution.

mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23

3 Answers3

3

One way to get it is something like

=LET(x, FILTERXML("<t><s>"&SUBSTITUTE($A$1, ",", "</s><s>")&"</s></t>", "//s"),
IF(SEQUENCE(1,2)=1, LEFT(x, SEARCH("x",x)-1), RIGHT(x, LEN(x)-SEARCH("x",x))))

enter image description here

Once you break up the string by comma, you can then break up the component strings by "x" with something like

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A7, "x", "</s><s>")&"</s></t>", "//s"))

but I'm not sure if you can combine the two actions in one go to get both width and depth dimensions (i.e. =TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(original_filterxml, "x", "</s><s>")&"</s></t>", "//s")) will not work).

EDS
  • 2,155
  • 1
  • 6
  • 21
  • 1
    Hey EDS - Your LET solves the problem. It is so simple and clear - I don't know why I did not come up with anything like it. I just saw JvdV's answer and decided that in reality, his is the answer only because of one (un-stated) criteria. It is better to have numerical value outcomes because the outputs are used in later calculations. I just wrapped yours into VALUE functions to get there and it works. Basically, you both approached the problem in the same way - the only difference is in text vs number. Nice work and, as always, thanks. – mark fitzpatrick Sep 06 '21 at 07:04
3

Maybe,

In C1, formula copied right to D1 and all copied down :

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&$A$1,"X",","),",",REPT(" ",99)),((ROW(A1)*2+COLUMN(A1))-2)*99,99))

Or,

If using FILTERXML function, try :

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A$1,"X",","),",","</b><b>")&"</b></a>","//b["&(ROW(A1)*2+COLUMN(A1))-2&"]"),"")

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • Hi Bosco - This produces the result, but it is connected to a range. The output has to be an array table. This will feed into the later calculations. Your first formula is totally foreign to me and really useful for non-365 versions. I can only vaguely recall methods pre-FILTERXML and I struggle when I encounter a need for splitting without it. Thanks for the answer. – mark fitzpatrick Sep 06 '21 at 07:00
3

Looks like you want to either spill the entire array or use it in later calculations? Either way, I came up with:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,",","x"),"x","</s><s>")&"</s></t>","//s"),INDEX(X,SEQUENCE(COUNT(X)/2,2)))

Or, a littel more verbose without LET():

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,",","x"),"x","</s><s>")&"</s></t>","//s"),SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"x","")),2))

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Hey JvDV - I really thought that if you answered, I would see an exotic xpath that did a nested parse. After reading your FILTERXML reference answer, I convinced myself that this was otherwise impossible. Hwvr, you & Euler cut the Gordian Knot. Many thanks for the answer. I will incorporate it into the answer made to [this](https://stackoverflow.com/questions/69045317/how-to-calculate-backplane-throughput-given-an-arbitrary-list-of-network-links/69067876#69067876) question to make the data entry easier. It is a much easier way to enter a "case" into the calculation. – mark fitzpatrick Sep 06 '21 at 07:13
  • @markfitzpatrick, unfortunately filterxml is not able to alter the sequence itself. Hopefully the linked reference made that clear. I thought at least I mentioned it somewhere in there – JvdV Sep 06 '21 at 07:33
  • I wasn't looking to alter it. I was looking at making a nested parse similar to your second answer where you parsed the outputs of the initial parse. So you have an outer parse based on "," and an inner parse based on "x". But your answer above achieves it. – mark fitzpatrick Sep 06 '21 at 08:04
  • 1
    @markfitzpatrick. Ah ok, well `FILTERXML()` will never be able to return a 2D-array, no matter if it would be possible with xpath expressions. Therefor we need Excel functionality (there are more ways than just 1). Look at it this way; even with just two elements in the order of 'A' and 'B', you won't be able to return 'B' before 'A' in the same 1D-array through `FILTERXML()`. We may be able to filter an array, but we cannot alter. – JvdV Sep 06 '21 at 11:46
  • In this case, it was simply to render the "qty1 x speed,1 qty2 x speed2, qty3 x speed3" into a two column table, which your approach delivers. I recall seeing a quite similar problem (in the real wold aot stack) with dimensions of office space, e.g. "3x4,7x12,5.7x8" that needed to be rendered into columns. In that case, I subst "," with "x" and then demultiplexed it, which... now that I think of it, is the approach recommended by you, Euler and Bosco. – mark fitzpatrick Sep 06 '21 at 11:56