Can you use SUBSTITUTE for many values without nesting?

Question

Is there a way to swap many instances of strings at once without nesting?

For instance, say I want to drop all instances of the following values from a string:

• Target
• Walmart
• CVS

---

Input String: "I went to Target instead of Walmart but I really wanted to go to CVS"
Output String: "I went to instead of but I really wanted to go to "

---

I tried =SUBSTITUTE(A1,H1:H3,"") where A1 is the input and H1:H3 are the strings to replace but this only swaps the first string

I know how to do this in VBA but this project cannot use VBA

Answer 1

There is no way with SUBSTITUTE outside of nesting to do what is wanted. In the future LAMBDA will be an option.

For now and for backwards compatibility, here is a UDF that creates a function that takes many inputs and replaces them with the desired output.

It uses a param array so one can also create individual replacements:

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

It defaults to a replace of ""

So in this instance:

=SUBALL(A1,H1:H3)

But we can also do where we specify the output:

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

Or we can put the options as strings in the formula itself with their desired replacements:

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

---

As with all UDF, there are some rules that must be followed. The pairs must have the same number of arguments. You CANNOT do:

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

It will fail. But:

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

Will work.

---

With SCAN and LAMBDA:

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

If we want to replace words with other words we can use:

=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)))

Answer 2

Update 27-4-'22:

Since LAMBDA() and it's helper functions have now been released to the production versions of ms365, one could use REDUCE():

=TRIM(REDUCE(A1,{"Target","Walmart","CVS"},LAMBDA(a,b,SUBSTITUTE(a,b,""))))

Or, even try:

=TEXTJOIN(" ",,TEXTSPLIT(A1,{" ","Target","Walmart","CVS"}))

But, be aware of possible false positives, however SUBSTITUTE() and TEXTSPLIT() are both case-sensitive so for these proper words it seemed to work out fine. To counter false positives, 1st change spaces to tripple spaces for example and go from there (or use the old answer which is still valid). Another option is to nest FILTER():

=TEXTJOIN(" ",,REDUCE(TEXTSPLIT(A1," ",,1),{"Target","Walmart","CVS"},LAMBDA(a,b,FILTER(a,a<>b))))

---

Old Answer (Still valid since it avoids false positives):

I guess I'd go with a "Yes it's possible, but..." answer. It may be a stretch but I noticed you haven't used any punctuation which lead me to believe we can split a string on the space and filter out the unwanted parts that way, piecing back together the wanted parts:

Formula in A2:

=TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s["&TEXTJOIN(" and ",,".!= '"&B1:B3&"'")&"]"))

Now you can add as many values to your range of unwanted strings without further adaptation to your formula.

• Note that this does require Excel 2019 or later for the TEXTJOIN() to work. With Excel 2019 you'd also need to confirm through CtrlShiftEnter.
• Also note that this will need some adaptation the minute you start using punctuation.
• A last remark is that FILTERXML() is case-sensitive.

Answer 3

I was hoping to find a manageable spreadsheet formula to do this but it sounds like VBA is needed until Lambda is available to the masses.

Public Function ARR_REPLACE(xInput As String) As String

Dim Arr
Arr = Array("Target", "Walmart", "CVS")

Dim i As Long

For i = LBound(Arr) To UBound(Arr)
    xInput = Replace(xInput, Arr(i), "", , , vbTextCompare)
Next i

ARR_REPLACE = xInput

End Function