一个单元格内, 将多个符合条件字段后后数字截取出来并求和

cfsonic

入主题所示,一个单元格内, 将多个符合条件字段后的数字截取出来并求和



这个诉求已经超出本人的能力范围,特请各位大神协助,不知道能不能通过公式实现, 加辅助列也无妨~!


先谢谢了~!

满坛皆为吾师

365版本，仅供参考

1. =VLOOKUP("*ADV*",MID(TEXTSPLIT(TEXTSPLIT(E3,":",");"),","),{1,4},66),{1,2},)

复制代码

低版本，仅供参考

1. =VLOOKUP("*ADV*",TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$3,");",",("),",(",REPT(" ",456)),ROW($1:$23)*456-455,456)),1,)

复制代码

cfsonic

=VLOOKUP("*ADV*",TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$3,");",",("),",(",REPT(" ",456)),ROW($1:$23)*456-455,456)),1,)

请教一下, 如果有多个字段,这次是ADV, 下次CM,或者DB 如何破解

quqiyuan

=-LOOKUP(9^9,-MID(VLOOKUP(D7,TEXTSPLIT($E$3,":",";"),2,),4,ROW($1:$15)))

ericxzhou

=LET(a,TEXTSPLIT(E3,{":",",","CNY"},";",1),FILTER(TAKE(a,,2),ISNUMBER(FIND("ADV",TAKE(a,,1)))))

渡渡orz

1. =--VLOOKUP("*ADV*",TEXTSPLIT($E$3,{":",",","CNY"},{";"}),3,)

复制代码

cfsonic

ericxzhou 发表于 2023-6-19 20:08
=LET(a,TEXTSPLIT(E3,{":",",","CNY"},";",1),FILTER(TAKE(a,,2),ISNUMBER(FIND("ADV",TAKE(a,,1)))))

谢谢回复, 但是没有安装365,用的版本是2021,textsplit 不能用,