Excel单单元格公式优化：处理重复垃圾字符串的有效内容提取问题

问题背景 #

Table2的[muddle]列包含混合垃圾词与无固定规则有效内容的字符串，垃圾词列表存储在Table1的[junkwords]列。原LET公式可正常提取非垃圾词内容，但当同一垃圾词重复出现时（如"woo"出现两次），因SEARCH函数仅能定位首个匹配位置，导致公式失效。需采用单单元格LET公式解决，禁止使用VBA、名称管理器或多层嵌套公式。

修正后的公式 #

=LET(
    muddle, Table2[muddle],
    junkList, Table1[junkwords],
    strLen, LEN(muddle),
    // 收集所有垃圾词的起始位置（含重复出现的实例）
    allJunkStarts, REDUCE("", junkList, LAMBDA(acc, word, 
        LET(start, SEARCH(word, muddle),
            IF(ISNUMBER(start), 
                REDUCE(acc, SEQUENCE(INT((strLen - start)/LEN(word)) + 1), LAMBDA(a, i,
                    LET(currentStart, start + (i-1)*LEN(word),
                        IF(currentStart + LEN(word) -1 <= strLen, a & "," & currentStart, a)
                    )
                ),
                acc
            )
        )
    )),
    // 转换为有序数值数组，去除无效空值
    junkStarts, SORT(FILTER(--TEXTSPLIT(allJunkStarts, ","), --TEXTSPLIT(allJunkStarts, ",")>0)),
    // 匹配对应垃圾词的长度，计算结束位置
    junkEnds, junkStarts + LEN(XLOOKUP(junkStarts, junkStarts, junkList, ,0,1)) - 1,
    // 补充首尾虚拟区域，确保覆盖开头/结尾的有效内容
    fullRanges, VSTACK({0,0}, HSTACK(junkStarts, junkEnds), {strLen+1, strLen+1}),
    // 筛选出有效内容的起止范围（排除空段）
    validRanges, FILTER(
        HSTACK(INDEX(fullRanges, SEQUENCE(ROWS(fullRanges)-1),2)+1, INDEX(fullRanges, SEQUENCE(ROWS(fullRanges)-1,2,2),1)-1),
        INDEX(fullRanges, SEQUENCE(ROWS(fullRanges)-1),2)+1 <= INDEX(fullRanges, SEQUENCE(ROWS(fullRanges)-1,2,2),1)-1
    ),
    // 提取并合并所有有效内容段
    TEXTJOIN(";; ", TRUE, MID(muddle, INDEX(validRanges,,1), INDEX(validRanges,,2)-INDEX(validRanges,,1)+1))
)

关键改进点 #

1. 捕获所有垃圾词实例：通过嵌套REDUCE遍历每个垃圾词，循环计算其在目标字符串中的所有出现位置，彻底解决原公式仅识别首次出现的问题。
2. 完整覆盖有效区域：补充字符串首尾的虚拟垃圾区域，确保能提取开头无垃圾词、结尾无垃圾词的有效内容。
3. 精准筛选有效段：通过计算相邻垃圾区域的间隙，自动排除因连续垃圾词产生的空段，仅保留实际有效内容。

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