我需要從單元格中的實際資料轉換單元格值：(
12345(5pcs),12111,121212(3pcs),,,, )
轉換為單個單元格：
12345,12345,12345,12345,12345,12111,121212,121212,121212
我需要創建一個公式來執行此任務，但我找不到任何解決方案。
uj5u.com熱心網友回復：
Office 365（在早期版本的 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))
uj5u.com熱心網友回復：
以與@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))
或者，如果可用，請使用SCAN()和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))
或者，與上面相同，但嵌套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))
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/335711.html