魔女马幼熙 发表于 2021-10-27 17:47:24

偷鸡：=LEFT(G1,2）
不偷鸡=SUBSTITUTE(LEFT(SUBSTITUTE(G1&",",",",REPT(" ",9)),9),-LOOKUP(,-RIGHT(LEFT(SUBSTITUTE(G1&",",",",REPT(" ",9)),9),ROW($1:$9))),)
以上针对5楼。。。楼顶的我仔细看看

神罚往昔 发表于 2021-10-31 17:25:54

俊霖 发表于 2021-11-2 14:01:31

=TRIM(MID(SUBSTITUTE($A$1,",",REPT(" ",100)),(ROW(A1)-1)*100+1,100))，单元格内容转置，然后使用=RIGHT(B1,LENB(B1)-LEN(B1))就可以实现了，其实用完第一个函数就直接可以Ctrl+E了

潇灑趉 发表于 2021-11-11 17:16:01

为啥对应的百分数没有提取出来呐

人类就是人累 发表于 2021-11-14 04:41:47

大佬棒棒嗒~~~~~学习了~~~（我总忘了大佬这个技巧~~~~）老套路来一遍：
=LEFTB(TRIM(MID(SUBSTITUTE(LOOKUP("座",D$2:D2),",",REPT(" ",99)),(ROW(A3)-MATCH("座",D$1:D2))*99-98,99)),IFERROR(SEARCHB("?",TRIM(MID(SUBSTITUTE(LOOKUP("座",D$2:D2),",",REPT(" ",99)),(ROW(A3)-MATCH("座",D$1:D2))*99-98,99)))-1,9))
JIVY大佬的抄一遍：
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(LOOKUP("座",D$2:D2),INDEX(MODE.MULT(ISERR(-MID(LOOKUP("座",D$2:D2),ROW($1:$99),1))+ROW($1:$99))-1,ROW()-MATCH("座",D$2:D2))),",",REPT(" ",9)),9)),"")
稍微改一下的也来一个：
=IFNA(VLOOKUP(",*",RIGHT(","&LEFT(LOOKUP("座",D$2:D2),INDEX(MODE.MULT(ISERR(-MID(LOOKUP("座",D$2:D2),ROW($1:$99),1))+ROW($1:$99))-1,ROW()-MATCH("座",D$2:D2))),ROW($1:$9)+{1,0}),2,),"")

毫无违和感 发表于 2021-11-19 10:25:57

偷鸡的公式是不可行的。

龙火 发表于 2021-11-23 17:38:12

不偷鸡的公式如果，第一个颜色是单色占比100,也不成立呀，像黑色100，提取出来是黑色1

星空的云 发表于 2021-11-24 13:37:25


=LEFT(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$D$2:$D$17),",",REPT(" ",199)),ROW(A1)*199-198,199)),LENB(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$D$2:$D$17),",",REPT(" ",199)),ROW(A1)*199-198,199)))-LEN(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$D$2:$D$17),",",REPT(" ",199)),ROW(A1)*199-198,199))))

卧室逆蝶 发表于 2021-11-28 15:57:17

有点特色的名字才容易被人记住 发表于 2021-12-2 12:32:28


=LEFT(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$D$2:$D$17),",",REPT(" ",199)),ROW(A1)*199-198,199)),LENB(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$D$2:$D$17),",",REPT(" ",199)),ROW(A1)*199-198,199)))-LEN(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$D$2:$D$17),",",REPT(" ",199)),ROW(A1)*199-198,199))))
页: 1 [2] 3 4
查看完整版本: 文本百分比数字符号混合提取并转置