excel - 您可以在不嵌套的情况下对许多值使用 SUBSTITUTE 吗？

Question

有没有办法一次交换多个字符串实例而不嵌套？
例如，假设我想从字符串中删除以下值的所有实例:

目标

沃尔玛

简历

输入字符串: “我去了 Target 而不是沃尔玛，但我真的很想去 CVS”
输出字符串: “我去而不是但我真的很想去”

我试过 =SUBSTITUTE(A1,H1:H3,"")在哪里 A1是输入， H1:H3是要替换的字符串，但这只会交换第一个字符串
我知道如何在 VBA 中做到这一点，但这个项目不能使用 VBA

Answer 1

在嵌套之外，没有办法用 SUBSTITUTE 来做想要的事情。 future LAMBDA将是一个选择。
现在和向后兼容，这里有一个 UDF，它创建了一个函数，该函数接受许多输入并将它们替换为所需的输出。
它使用一个参数数组，因此也可以创建单独的替换:

Function SUBALL(str As String, ParamArray arr() As Variant) As String
    Dim i As Long
    For i = LBound(arr) To UBound(arr) Step 2
        Dim rngarr As Variant
        rngarr = arr(i)
        
        If UBound(arr) > i Then
            Dim rpArr As Variant
            rpArr = arr(i + 1)
        Else
            Dim df As Boolean
            df = True
        End If
        
        If TypeName(rngarr) = "String" Then
            If df Then
                str = Replace(str, rngarr, "")
            Else
                str = Replace(str, rngarr, rpArr)
            End If
        Else
            Dim j As Long
            For j = LBound(rngarr, 1) To UBound(rngarr, 1)
                If df Then
                    str = Replace(str, rngarr(j, 1), "")
                Else
                    str = Replace(str, rngarr(j, 1), rpArr(j, 1))
                End If
            Next j
        End If
    Next i
    
    SUBALL = str
End Function

它默认替换为 ""所以在这种情况下:

=SUBALL(A1,H1:H3)

但是我们也可以在我们指定输出的地方做:

=SUBALL(A1,H1:H3,I1:I3)

或者我们可以将选项作为字符串放在公式本身中，并带有所需的替换:

=SUBALL(A1,"Target","MyVal","Walmart","","CVS","Long Receipt Place")

与所有 UDF 一样，必须遵循一些规则。这些对必须具有相同数量的参数。你不能这样做:

=SUBALL(A1,H1:H3,"Word")

它会失败。但:

=SUBALL(A1,H1:H3,{"Word";"Word";"Word"})

将工作。


使用 SCAN 和 LAMBDA:

=LET(rpl,H1:H3,str,A1,INDEX(SCAN(str,rpl,LAMBDA(a,b,SUBSTITUTE(a,b,""))),COUNTA(rpl)))

如果我们想用其他单词替换单词，我们可以使用:

=LET(orig,H1:H3,rpl,I1:I3,str,A1,cnt,COUNTA(rpl),INDEX(SCAN(str,SEQUENCE(cnt),LAMBDA(a,b,SUBSTITUTE(a,INDEX(orig,b),INDEX(rpl,b)))),COUNTA(rpl)))