提取系数并求和

月山少

例如：WDZA-YJY-3*120+2*70
系数为3，2
系数和为=3+2=5
具体文件如附件
提取系数并求和.zip (7.65 KB, 下载次数: 12)

2021-10-8 15:17 上传

点击文件名下载附件
提取系数并求和.zip

猴子阁下

=SUMPRODUCT(--TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(B2&"a*a*a*a*","*",REPT(" ",50)),ROW($1:$5)*50-49,50)),"-","+"),"+",REPT(" ",5)),5),"0;;;!0"))+1-MMULT(LEN(B2)-LEN(SUBSTITUTE(B2,{"*","+"},)),{1;-1})

太没规则了。。只会火车

老大有你摸仿不来的霸气

=SUMPRODUCT(--TEXT(LEFT(SUBSTITUTE(0&TEXT(TRIM(MID(SUBSTITUTE(MID(B2,10,99),"+",REPT(" ",99)),ROW($1:$9)*99-98,99))&"e0","1"),"*",REPT(" ",99)),99),"0;0;0;!1"))

清清流水

1.假设前面固定9位，从第10位起是需要的：
=SUM(IFERROR(--(0&LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(MID(B2&"+",10,99),"+","@"&REPT(" ",99)),ROW($1:$9)*99-98,99)),"*",REPT(" ",9)),9)),1))
2.假设系数最多两位：
=SUM(ISNUMBER(FIND({"-","+"}&ROW($1:$99)&"*",B2))*ROW($1:$99),LEN(SUBSTITUTE(B2,{"+","*"},))*{-1,1})+1

蒙挚

又没花了~~~继续欠着~~~明天再补
刚刚那个不设任何条件的公式有BUG，放在这里吧——只需要满足不同数之间是用+号相连就行。。。
=SUM(IFERROR(ABS(MID(TEXT(MID(SUBSTITUTE(B2&"@","*",REPT(" ",9)),ROW($1:$99),9),),3,9))/2,),LEN(SUBSTITUTE(B2,{"+","*"},))*{-1,1},1)

胡顺民

我来给你小红花

袁海泉

系数在 9 以内的。。。。。
=SUM(--TEXT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(B2,"*",".0"),"+",REPT(" ",50)),{10,60,110},50))&0,"[>9]1;0;0;1"))
扩大系数量：
=SUM(--TEXT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(B2,"*",".0"),"+",REPT(" ",50)),{10,60,110},50))&"00","[>99]1;0;0;1"))

想起琴声

=SUM(IFERROR(LEFT(TEXT(TRIM(MID(SUBSTITUTE(MID(B2,10,20),"+",REPT(" ",99)),99*ROW($1:$9)-98,99))&0,"1;;0;@"))/1,1))
数组三键下拉。