求取数公式

pzhred

求红色区域的取数公式，取出结果为黄色取域

ssg365

A列：=REPT("*",ISNUMBER(FIND("*",LOOKUP(1,0/(LEN(LEFT(E$3:E3,FIND(" ",E$3:E3)-1))>2),E$3:E3))))

B列:=SUBSTITUTE(LEFT(LOOKUP(1,0/(LEN(LEFT(E$3:E3,FIND(" ",E$3:E3)-1))>2),E$3:E3),FIND(" ",LOOKUP(1,0/(LEN(LEFT(E$3:E3,FIND(" ",E$3:E3)-1))>2),E$3:E3))-1),"*",)

C列：=SUBSTITUTE(LOOKUP(1,0/(LEN(LEFT(E$3:E3,FIND(" ",E$3:E3)-1))>2),E$3:E3),A3&B3&" ",)

D列：=IF(LEN(LEFT(E3,FIND(" ",E3)-1))>2,"",MID(INDEX(FILTER(E$3:E$18,LEN(LEFT(E$3:E$18,FIND(" ",E$3:E$18)-1))<3),SUM(N(D$2:D2<>""))),4,99))

pzhred

谢谢大神的公式，abc列非常好用，仅d列公式有点问题，取不出数据。

一剑惊心

A3=TEXT(LEFT(LOOKUP(2,1/MMULT(N(MID(E$3:E3,{4,5},1)=" "),{1;1}),E$3:E3)),)
B3=RIGHT(TRIM(LEFT(LOOKUP(2,1/MMULT(N(MID(E$3:E3,{4,5},1)=" "),{1;1}),E$3:E3),4)),3)
C3=TRIM(MID(LOOKUP(2,1/MMULT(N(MID(E$3:E3,{4,5},1)=" "),{1;1}),E$3:E3),5,99))
或=TRIM(MID(VLOOKUP("~"&A3&B3&"*",E:E,1,),5,99))
D4=IF(MID(E3,3,1)=" ",MID(E3,4,99),"")