Parse a string into a Table using FILTERXML

user_4617121 0
Viewed 193

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.

excel-formula excel
3 Answers

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

user_109627890

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

user_121251810

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

user_97581940
Related