Passing multiple strings to variable in Replace Function

I am trying to remove the operators and funtions from a formula string. I am
using the Replace Method. However I cannot seem to pass multiple items to
two variables and then have those variables work properly within the two
Replace functions. The goal being to start with a string that looks like
this:

"=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$3)"

and end up looking like this:

"=$A7 $B$11 $I$2:$I$6 I6 1,3,3,$I$6,K$3 "

Can anyone provide me with some guidance.

Thanks

EM

Sub FormulaZapper()
Dim CurrentFormula As String
Dim CurrentFormulaNoOperators As String
Dim CurrentFormulaNoFunction As String
Dim Operators As String
Dim AllFunctions

AllFunctions = Array("SUM", "CHOOSE")

Operators = "=" & "/" & "+" & "-" & "*" & "^" & & "(" & ")"

CurrentFormula = "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$3)"

CurrentFormulaNoOperators = Replace(CurrentFormula, Operators, "")
CurrentFormulaNoFunction = Replace(CurrentFormula, AllFunctions, "")

End Sub

Sub FormulaZapper()
Dim CurrentFormula As String
Dim CurrentFormulaNoOperators As String
Dim CurrentFormulaNoFunction As String
Dim Operators As Variant
Dim AllFunctions As Variant
Dim s As String

AllFunctions = Array("SUM", "CHOOSE")

Operators = Array("=", "/", "+", "-", "*", "^", "(", ")")

CurrentFormula = "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$3)"
s = CurrentFormula
For i = LBound(Operators) To UBound(Operators)
s = Replace(s, Operators(i), " ", 1, -1, vbTextCompare)
Next
CurrentFormulaNoOperators = s
For i = LBound(AllFunctions) To UBound(AllFunctions)
s = Replace(s, AllFunctions(i), " ", 1, -1, vbTextCompare)
Next
CurrentFormulaNoFunction = s
Debug.Print s
End Sub

Produces:
$A7 $B$11 $I$2:$I$6 I6 1,3,3,$I$6,K$3

--
Regards,
Tom Ogilvy


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I am trying to remove the operators and funtions from a formula string. I
am
> using the Replace Method. However I cannot seem to pass multiple items to
> two variables and then have those variables work properly within the two
> Replace functions. The goal being to start with a string that looks like
> this:
>
> "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$3)"
>
> and end up looking like this:
>
> "=$A7 $B$11 $I$2:$I$6 I6 1,3,3,$I$6,K$3 "
>
> Can anyone provide me with some guidance.
>
> Thanks
>
> EM
>
> Sub FormulaZapper()
> Dim CurrentFormula As String
> Dim CurrentFormulaNoOperators As String
> Dim CurrentFormulaNoFunction As String
> Dim Operators As String
> Dim AllFunctions
>
> AllFunctions = Array("SUM", "CHOOSE")
>
> Operators = "=" & "/" & "+" & "-" & "*" & "^" & & "(" & ")"
>
> CurrentFormula = "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$3)"
>
> CurrentFormulaNoOperators = Replace(CurrentFormula, Operators, "")
> CurrentFormulaNoFunction = Replace(CurrentFormula, AllFunctions, "")
>
> End Sub