月山少 发表于 2021-10-8 15:17:00

提取系数并求和



例如：WDZA-YJY-3*120+2*70
系数为3，2
系数和为=3+2=5
具体文件如附件

猴子阁下 发表于 2021-10-17 21:55:48

=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})

太没规则了。。只会火车

老大有你摸仿不来的霸气 发表于 2021-10-27 00:22:37

=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"))

清清流水 发表于 2021-10-27 23:38:39

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

蒙挚 发表于 2021-11-30 15:45:18

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

胡顺民 发表于 2021-12-7 02:35:17

我来给你小红花

袁海泉 发表于 2021-12-20 10:02:30

系数在 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"))

想起琴声 发表于 2021-12-22 00:03:07

=SUM(IFERROR(LEFT(TEXT(TRIM(MID(SUBSTITUTE(MID(B2,10,20),"+",REPT(" ",99)),99*ROW($1:$9)-98,99))&0,"1;;0;@"))/1,1))
数组三键下拉。
页: [1]
查看完整版本: 提取系数并求和