Replace all non-operators from string

[ExcelMonkey]

Does anyone know how to replace all non-operators from a text string? That
is if my string is:

"A1+SUM(B1:B30)-365*Average(Z1:Z30)/$F$4+CostCell"

I want to replace all characters that are not +,-,*,/,^ with "".

Thanks

EM

 

[Rick Rothstein \(MVP - VB\)]

Does anyone know how to replace all non-operators from a text string?

That
is if my string is:

"A1+SUM(B1:B30)-365*Average(Z1:Z30)/$F$4+CostCell"

I want to replace all characters that are not +,-,*,/,^ with "".

This function should do what you want...

Function Parse(TextString As String) As String
Dim X As Long
Parse = TextString
For X = Len(Parse) To 1 Step -1
If InStr("+-*/^", Mid(Parse, X, 1)) = 0 Then
Parse = Replace(Parse, Mid(Parse, X, 1), "")
End If
Next
End Function

Rick

 

[Ron Rosenfeld]

Does anyone know how to replace all non-operators from a text string? That
is if my string is:

"A1+SUM(B1:B30)-365*Average(Z1:Z30)/$F$4+CostCell"

I want to replace all characters that are not +,-,*,/,^ with "".

Thanks

EM

You can use this UDF.

To enter it, <alt-F11> opens the VB Editor. Ensure your project is highlighted
in the Project Explorer window, then Insert/Module and paste the code below
into the window that opens.

To use this, enter the function =reNonOps(str) where str is either your string,
or a cell reference containing the string.

=====================================================
Function reNonOps(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^\-+*/^]"
reNonOps = re.Replace(str, "")
End Function
==================================
--ron