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" wrote in message
...
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