答案1
对于提取此数组公式:
=TEXTJOIN(" ",TRUE,IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(A12," ",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A12)-LEN(SUBSTITUTE(A12," ",""))+1))-1)*999+1,999)),$B$9:$D$9&"",0)),TRIM(MID(SUBSTITUTE(A12," ",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A12)-LEN(SUBSTITUTE(A12," ",""))+1))-1)*999+1,999)),""))
作为数组公式,退出编辑模式时需要使用 Ctrl-Shift-Enter 而不是 Enter 进行确认。
这将迭代每个“单词”,然后尝试在范围内找到它,如果找到,则将其返回给 TEXTJOIN。
然后计数:
=LEN(B12)-LEN(SUBSTITUTE(B12," ",""))+1