如何用CSE数组公式提取满足Fruit=Y条件的引号内字符串？

假设目标输入字符串位于单元格A1，可以使用以下CSE数组公式实现需求：

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("Fruit=Y",SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)-LEN(A1)+1,LEN(A1)))," ",""))),MID(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)-LEN(A1)+1,LEN(A1))),FIND("""",TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)-LEN(A1)+1,LEN(A1))))+1,FIND("""",TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)-LEN(A1)+1,LEN(A1))),FIND("""",TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)-LEN(A1)+1,LEN(A1))))+1)-FIND("""",TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)-LEN(A1)+1,LEN(A1))))-1),""))

公式说明 #

1. 拆分独立条目：通过SUBSTITUTE(A1,",",REPT(" ",LEN(A1)))将所有逗号替换为与原字符串等长的空格，再结合MID和ROW序列，把原字符串拆分成一个个独立的条目（如"Apple" Fruit=Y）。
2. 筛选符合条件的条目：对每个条目先执行TRIM去除前后空格，再用SUBSTITUTE删除所有空格，最后通过SEARCH检查是否包含Fruit=Y，以此筛选出标注为Y的条目。
3. 提取引号内文本：对筛选出的条目，用FIND定位前后双引号的位置，再用MID提取引号之间的目标文本。
4. 合并结果：使用TEXTJOIN将所有提取到的文本用, 连接成最终字符串。

注意：输入公式后需按Ctrl+Shift+Enter（CSE）完成数组公式的输入；Excel 365/2021版本支持动态数组，直接回车即可生效。

内容的提问来源于stack exchange，提问作者Htnasarp