No need to use VBA anymore for this! Super simple formula now!

=REDUCE(A1,2:6,LAMBDA(a,b,SUBSTITUTE(a,b,OFFSET(b,0,1))))

Where A1 is the target cell to replace text b2:b6 is where the keywords are stored (texts to be replaced) c2:c6 (referenced by the offset) is where the replacing texts are stored

Credit: Chandoo

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

It's also possible to use nested SUBSTITUTE() functions to preserve a character in some instances but change it in others:

In cell A1 I have:

Comedy, Music, Bonus Features

In cell B1 I want:

comedy music bonus.features

One formula to achieve this is:

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1); ", "; "|"); " "; "."); "|"; " "))

The first substitution replaces a comma and space (", ") with a character not likely to be found in cell A1: "|".

The second substitution replaces all spaces with periods.

The third substitution replaces all | characters with spaces.

The order is important so that wanted spaces aren't replaced with periods.

The TRIM() function ensures that no trailing spaces in A1 are converted to periods.

Another way to achieve this with fewer steps is to use this formula:

=LOWER(SUBSTITUTE(SUBSTITUTE(TRIM(A1); " "; "."); ",."; " "))

In this case, the first substitution replaces all the spaces with periods, which results in:

comedy,.music,.bonus.features

The second substitution replaces all instances of ",." with a space to get the desired result:

comedy music bonus.features