remove unwanted characters and evaluate remaining in excel

Question

I want to be able to evaluate a formula in a cell (in excel) that has descriptions inbetween the values. For example, I'll have 3hrs*2ppl + 4 in the cell, and I'll want to evaluate it in another cell to return the numerical answer (eg. 10) in this case.

Can anyone help me?

Ps. I don't know much about Visual Basic but can create the macro if the code is already written for me.

score 1 · Answer 1 · answered Mar 18 '14 at 22:12

Try this vba function. It will only work for the four basic operations.

 Function sEvalFormula(sInput As String) As String
Dim lLoop As Long, sSpecialChars As String, sTemp As String

sSpecialChars = "0123456789-+*/()=."


For lLoop = 1 To Len(sInput)
    If InStr(sSpecialChars, Mid(sInput, lLoop, 1)) > 0 Then sTemp = sTemp & Mid(sInput, lLoop, 1)
Next

sEvalFormula = Evaluate(sTemp)


End Function

score 1 · Answer 2 · answered Mar 18 '14 at 22:12

First, enter the following UDF in a standard module:

Public Function evall(s As String) As Variant
    l = Len(s)
    s2 = ""
    For i = 1 To l
        ch = Mid(s, i, 1)
        If ch Like "[a-z]" Then
        Else
            s2 = s2 & ch
        End If
    Next i
    evall = Evaluate(s2)
End Function

So if A1 contains:

3hrs*2ppl + 4

then

=evall(A1)

will display 10

This UDF discards lower case letters and evaluates the remaining expression.

score 1 · Answer 3 · edited May 23 '17 at 11:49

Another way to do this is by using regular expressions (Regex). See this link for more information on what the patterns mean.

Also, make sure you add ”Microsoft VBScript Regular Expressions 5.5″ as reference to your VBA module (see this link for how to do that).

I'm passing in a cell address, stripping out any letters, and then evaluating the function.

Function RegExpReplace(cell As Range, _
        Optional ByVal IsGlobal As Boolean = True, _
        Optional ByVal IsCaseSensitive As Boolean = True) As Variant

    'Declaring the object
    Dim objRegExp As Object

    'Initializing an Instance
    Set objRegExp = CreateObject("vbscript.regexp")

    'Setting the Properties
    objRegExp.Global = IsGlobal
    objRegExp.pattern = "[a-zA-Z]"
    objRegExp.IgnoreCase = Not IsCaseSensitive

    'Execute the Replace Method
    RegExpReplace = Evaluate(objRegExp.Replace(cell.Text, ""))
End Function

In Cell B1 enter this formula: =RegExpReplace(A1)

Results:

enter image description here

remove unwanted characters and evaluate remaining in excel

3 Answers3