在给定的单元格计算式中依次提取数字

为民为世

除法和括号怎么算？

能源藻类

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D$1,"-","+-"),"*","+"),"/","+"),"+",REPT(" ",100)),ROW(A1)*100-98,100))

陈志江

示例文件.zip (18.68 KB, 下载次数: 121)

2021-10-6 15:09 上传

点击文件名下载附件
示例文件.zip
如附件，用POWERQUERY做的，支持乘法拆开

小小先森

新函数搞定

学习雷锋好榜样

冬哥，牛B

猩人福利

=IFNA(LOOKUP(9999,--LEFT(LOOKUP(A4,MMULT(-(ROW($1:$99)>=COLUMN(A:CU)),-TEXT(RIGHT(SUBSTITUTE(9999999&TRIM(MID(SUBSTITUTE(SUBSTITUTE("+"&MID(FORMULATEXT(A$1),2,999),"-","+"),"+",REPT(" ",99)),ROW($1:$99)*99-98,99)),"*",REPT(" ",20)),6),"[<99];1")),MID("+"&MID(FORMULATEXT(A$1),2,999),FIND("@",SUBSTITUTE(SUBSTITUTE("+"&MID(FORMULATEXT(A$1),2,999),"-","+"),"+","@",ROW($1:$99))),8)),ROW($1:$8))),"")

好多年没整这么长的公式了，就不简化了。

梦醒了心碎了

=LOOKUP(10^9,--MID(D$1,SMALL(IF(ISERR(-(0&MID(D$1,ROW($1:$299),1))),ROW($1:$299),999),ROW(A1))+(ROW(A1)=1),ROW($1:$9)))

阿洛舒华

顺通达

=IFNA(LOOKUP(9999,--LEFT(LOOKUP(A4,MMULT(-(ROW($1:$99)>=COLUMN(A:CU)),-TEXT(RIGHT(SUBSTITUTE(9999999&TRIM(MID(SUBSTITUTE(SUBSTITUTE("+"&MID(FORMULATEXT(A$1),2,999),"-","+"),"+",REPT(" ",99)),ROW($1:$99)*99-98,99)),"*",REPT(" ",20)),6),"[<99];1")),MID("+"&MID(FORMULATEXT(A$1),2,999),FIND("@",SUBSTITUTE(SUBSTITUTE("+"&MID(FORMULATEXT(A$1),2,999),"-","+"),"+","@",ROW($1:$99))),8)),ROW($1:$8))),"")

好多年没整这么长的公式了，就不简化了。

哥哥的后半生

=LOOKUP(10^9,--MID(D$1,SMALL(IF(ISERR(-(0&MID(D$1,ROW($1:$299),1))),ROW($1:$299),999),ROW(A1))+(ROW(A1)=1),ROW($1:$9)))