Excel: convert values to comma seperated in excel-CodePudding

I need to convert cell values from actual data in a cell :
( 12345(5pcs),12111,121212(3pcs),,,, )

Convert to in single cell:
12345,12345,12345,12345,12345,12111,121212,121212,121212

I need to create a formula to perform this task and I am not able to find any solution.

CodePudding user response：

Office 365 (this will be virtually undoable using formulas alone in earlier versions of Excel):

=LET(t,"<a><b>",u,"</b><b>",v,"</b></a>",w,SUBSTITUTE(A1,",",u),x,FILTERXML(t&SUBSTITUTE(w,"(",u)&v,"//b[number()=.]"),y,FILTERXML(t&w&v,"//b"),z,CONCAT(REPT(x&",",-IFERROR(MID(SUBSTITUTE(y,"pcs",""),FIND("(",y),9),-1))),LEFT(z,LEN(z)-1))

CodePudding user response：

In the same fashion as @JosWoolley:

=LET(A,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[node()]"),B,IFERROR(--A&"(1",SUBSTITUTE(A,"pcs)","")),C,FIND("(",B),D,LEFT(B,C-1),E,MID(B,C 1,LEN(A)),F,CONCAT(REPT(D&",",E)),LEFT(F,LEN(F)-1))

or, if available yet, use SCAN() and LAMBDA():

=LET(A,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[node()]"),SUBSTITUTE(CONCAT(SCAN("",A&"(1",LAMBDA(a,b,REPT(","&LEFT(b,FIND("(",b)-1),-LOOKUP(1,-MID(b,FIND("(",b) 1,ROW($1:$99))))))),",","",1))

Or, the same as above, but a nested LET():

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[node()]"),SUBSTITUTE(CONCAT(SCAN("",X&"(1",LAMBDA(a,b,LET(Y,FIND("(",b),REPT(","&LEFT(b,Y-1),-LOOKUP(1,-MID(b,Y 1,ROW($1:$99)))))))),",","",1))