文本:灶具468.37元,食堂清洁剂186.5六七月用,食材50+260+35.5,某个人支8000,600,490,……。求:用公式取出文本中的所有数字并求和,只能用公式,宏代码我这用不了。
B2公式如下,数组公式,输完按CTRL+SHIFT+ENTER生成数组,不管文本内数字有多复杂,也不管是否存在小数,更不用在逗号","后面手工加一个空格,全都能准确找出数字串,并且在一个单元格内完成提取数字并求和。
B2公式:
=SUMPRODUCT(IFERROR(--LEFT(MID(A2,ROW($1:$300),20),FIND(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A2,ROW($1:$300),20),0,),1,),2,),3,),4,),5,),6,),7,),8,),9,),".",),1),MID(A2,ROW($1:$300),20))-1),0)*ISERR(-MID(SUBSTITUTE(A2,".",0),ROW($1:$300)-1,1)))
b1加个辅助单元格,b1=SUMPRODUCT(ISERROR(--(0&MID(A1,ROW(1:999),1)))*ISNUMBER(--(0&MID(A1,ROW(2:1000),1))))回车,目的是计算出有几个连续的数字段,同时让c1公式相对简洁点
c1=SUM(--MID(A1&"a",SMALL(IF(ISERROR(--(0&MID(A1&"a",ROW(1:999),1)))*ISNUMBER(--(0&MID(A1&"a",ROW(2:1000),1))),ROW($2:$1000)),ROW(INDIRECT("1:"&B1))),SMALL(IF(ISNUMBER(--(0&MID(A1&"a",ROW(1:999),1)))*ISERROR(--(0&MID(A1&"a",ROW(2:1000),1))),ROW($1:$999)),ROW(INDIRECT("1:"&B1)))-SMALL(IF(ISERROR(--(0&MID(A1&"a",ROW(1:999),1)))*ISNUMBER(--(0&MID(A1&"a",ROW(2:1000),1))),ROW($2:$1000)),ROW(INDIRECT("1:"&B1)))+1))数组公式,同时按ctrl+shift+回车结束即可
你的数据结构也太不规范了啊,账务数据可不是这么玩的啊!!
给你一个数组公式,在B2单元格输入数组公式:
=EVALUATE(SUBSTITUTE(TRIM(TRIM(SUBSTITUTE(TEXTJOIN("",TRUE,IFERROR(IF(CODE(MID(A2,ROW($1:$99),1))>100," ",MID(A2,ROW($1:$99),1)),"")),"+"," ")))," ","+"))
是数组公式!!!必须是CTRL+SHIFT+ENTER这3个键同按结束,否则公式无效的哦。
或者,你可以使用比较严密一些的规范公式:
=IFERROR(EVALUATE(SUBSTITUTE(TRIM(TRIM(SUBSTITUTE(TEXTJOIN("",TRUE,IFERROR(IF(ABS(CODE(MID(A2,ROW($1:$100),1))-52)>6," ",MID(A2,ROW($1:$100),1)),"")),"+"," ")))," ","+")),"")
复制并下拉,即可…………